Support the ongoing development of Laravel.io →
posted 10 years ago
Eloquent
Last updated 2 years ago.
0
SELECT
	countries.id, countries.name, COUNT( countries_and_users.user_id ) AS total_users_country
FROM
	countries
	LEFT JOIN countries_and_users
		ON countries_and_users.country_id = countries.id
GROUP BY
	countries.id
ORDER BY
	countries.name
;
Last updated 2 years ago.
0

Here, I assume you use softDelete in User Model. The selectRaw won't be aware of it when querying because it is scoped with the Country model. That is why we need to omit the softly deleted users manually

Country::selectRaw('countries.*, count(notSoftDeletedUsers.country_id) AS count')->
leftJoin(DB::raw('(select * from users where deleted_at is null) as notSoftDeletedUsers'),'countries.id', '=', 'notSoftDeletedUsers.country_id')->
groupBy('countries.id')->
orderBy('count','DESC')->
paginate(5)
Last updated 9 years ago.
0

I would do it this way:

Country::leftJoin('users', 'users.country_id', '=', 'countries.id')
    ->groupBy('countries.id')
    ->orderBy('users_count', 'DESC')
    ->get(['countries.*', \DB::raw('COUNT(`' . \DB::getTablePrefix() . 'users`.`id`) AS `users_count`')]);

The query produced by this instruction will be as follows:

select `countries`.*,
       COUNT(`users`.`id`) AS `users_count`
from `countries`
left join `users`
       on `users`.`country_id` = `countries`.`id`
group by `countries`.`id`
order by `users_count` desc

By the way, I know Laravel produces lowercase SQL functions names, but I prefer to stick to the standard and use uppercase.

0

Sign in to participate in this thread!

Eventy

Your banner here too?

supernaut supernaut Joined 4 Mar 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.