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
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.