Kristo Kristo - 5 months ago 80
SQL Question

Laravel Eloquent count many to many relations

While having a users, companies and company_user pivot table: How to get companies sorted by their users count?

The following query is almost it. Only problem here is I just get companies who at least have one user. Every company without a user relation isnĀ“t taken into account.

$companies = Company::->with('users')
->join('company_user', 'companies.id', '=', 'company_user.company_id')
->selectRaw('companies.*, count(*) as `aggregate`')
->groupBy('companies.id')
->orderBy('aggregate', 'desc')
->get();


Example output should be:

company_1 (users: 4)
company_2 (users: 2)
company_3 (users: 0)
company_4 (users: 0)


Output with above query is:

company_1 (users: 4)
company_2 (users: 2)

Answer

It looks like some of your companies are being removed by the INNER JOIN. If so, you can try using a LEFT JOIN along with SUM (to count cardinality of the rows) to retain the missing data:

$companies = Company::->with('users')
    ->leftJoin('company_user', 'companies.id', '=', 'company_user.company_id')
    ->selectRaw('companies.*,
                 sum(case when company_user.company_id is null then 0 else 1 end) as `aggregate`')
    ->groupBy('companies.id')
    ->orderByRaw('sum(case when company_user.company_id is null then 0 else 1 end) desc')
    ->get();

I initially was also trying to use COUNT() as you were, but it seemed to always insist on counting NULL rows as 1. Instead, I switched to SUM() with a CASE expression and it worked fine.

Follow the link below for a running demo:

SQLFiddle