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
;
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)
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.
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community