vincent15000's avatar

Strange SQL query from Eloquent

Hello,

I have this query.

$companies = Company::
    with('admin')
    ->withCount('users', 'products', 'sales')
    ->orderBy('name')
    ->get();

And the corresponding SQL query is.

select `companies`.*, (select count(*) from `users` where `companies`.`id` = `users`.`company_id` and `users`.`deleted_at` is null) as `users_count`, (select count(*) from `products` where `companies`.`id` = `products`.`company_id` and `company_id` is null and `products`.`deleted_at` is null) as `products_count`, (select count(*) from `sales` where `companies`.`id` = `sales`.`company_id` and `company_id` is null) as `sales_count` from `companies` order by `name` asc

Why does Eloquent add and company_id is null ?

That has no sense.

Thanks for your answer.

V

1 like
4 replies
Tray2's avatar

I agree it seems odd, but my guess is that it has to do with generalization of the queries.

I have seen similar behaviour when using find it adds limit = 1 for some reason.

1 like
Snapey's avatar

if you make the with count an array() ?

->withCount(['users', 'products', 'sales'])

or as separate statements?

->withCount('users')
->withCount('products')
->withCount( 'sales')
1 like
drgreen's avatar

Could it be that the Company model relationships contain something like this?

->whereNull('company_id');
1 like
EricZwart's avatar

Strange indeed, did you put a scope on a model somewhere?

1 like

Please or to participate in this conversation.