Laravel Query Builder::whereNotNull causing repeating WHERE NOT NULL statements?

1k views Asked by At

I am getting weird behaviour and not sure if it is something I am doing wrong or a bug in Laravel query builder.

Whenever I am using the Builder::whereNotNull method, I seem to always have at least one extra WHERE NOT NULL statement in my query.

Example Code:

Method to sync all the customer contacts:

public function doStuff(CustomerRepository $customers)
{
    $count = $customers->count();

    $pages = (integer) ceil($count / $this->pageSize);

    for ($page = 0; $page < $pages; $page++) {
        $contacts = $customers->page($page * $this->pageSize, $this->pageSize);
        // yada-yada
    }
}

Relevant methods in the Customer Repository class:

public function __construct(DatabaseManager $databaseManager)
{
    $this->customers = $databaseManager->connection('sqlsrv')
        ->table('dbo.entity as Contact')
        ->select($this->select)
        ->leftJoin('dbo.entity AS Parent', 'Contact.COMPANY_ID', '=', 'Parent.ENTITY_ID')
        ->whereNotNull('Contact.email');
}

public function page($offset = 0, $count = 1000)
{
    if ($count > 1000) {
        $count = 1000;
    }

    return $this->customers
        ->skip($offset)
        ->take($count)
        ->get();
}

Eventually results in a query like this:

    select * from (
        select [Contact].[email],
            [Parent].*,
            row_number() over (order by (select 0)) as row_num
        from [dbo].[entity] as [Contact]
        left join [dbo].[entity] as [Parent] on [Contact].[COMPANY_ID] = [Parent].[ENTITY_ID]
        where [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null and [Contact].[email] is not null
        ) as temp_table
    where row_num between 64001 and 65000
1

There are 1 answers

0
Tinh Dang On

I must related to the looping for. cause I see each time looping the sql query will be appended one more "where not null" (65 times looping, 65 times appearing of "where not null"

Did you try to use different method than whereNotNull, I think the issue is not about the method whereNotNull. It must be related to the looping.

I need to see the entire class and the way you created object from that class to solve the problem.