Laravel query - order by count

Question

I want to select all the users that have the type of student and count all the countries that are connected to them. The order should be based on the number of countries linked to them.

Users table:

id    name
1     user1
2     user2
3     user3
4     user4
5     user5

Countries table:

id     country_name
1      America
2      Australia
3      Argentina
4      Afghanistan
5      India

pivot_countries_user table:

id     user_id     country_id
1      1           1
2      1           2
3      2           1
4      3           1
5      4           2
6      5           2
7      4           3
8      1           4

user_type table:

id    type       user_id
1     student    1
2     student    2
3     teacher    3
4     lawyer     4
5     teacher    5

Here's the laravel query that I tried:

DB::table('users')
->leftjoin('pivot_countries_user','pivot_countries_user.user_id','=','users.id')
->leftjoin('countries','countries.id','=','pivot_countries_user.id')
->leftjoin('user_type','user_type.user_id','=','users.id')
->select('users.name','users.type',
  DB::raw('count(pivot_countries_user.country_id)')) // should be per user but I don't know how

Expected output:

name      type       total_countries
user1     student    3
user2     student    1

Possible duplicate of Laravel MySQL orderBy count

Muhammad Arif
– Muhammad Arif

2016-04-11 07:42:40 +00:00
Commented Apr 11, 2016 at 7:42 — Muhammad Arif
– Muhammad Arif, Commented Apr 11, 2016 at 7:42

Pechi Muthu Haridas · Accepted Answer · 2016-04-11 09:08:20Z

2

DB::table('users')
->leftJoin('pivot_countries_user','pivot_countries_user.user_id','=','users.id')
->leftJoin('countries','countries.id','=','pivot_countries_user.country_id')
->leftJoin('user_type', function ($join) {
            $join->on('user_type.user_id', '=', 'users.id')
                 ->where('user_type.type', '=', 'student');
        })
->select('users.name','user_type.type',
  DB::raw('count(countries.country_id) AS total_countries'))
->groupBy('users.id')
->get();

then you will get expected result:
name   type         total_countries
user1   student    3
user2   student    1

answered Apr 11, 2016 at 9:08

Pechi Muthu Haridas

515 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

The following query generates your expected output.

Query Builder:

\DB::table('users as u')
            ->leftJoin('pivot_countries_user as uc','uc.user_id','=','u.id')
            ->leftJoin('user_type as ut','ut.user_id','=','u.id')
            ->select([
                'u.name',
                'ut.type',
                \DB::raw('(SELECT count(*) FROM uc WHERE uc.user_id=u.id) as total_countries')])
            ->orderBy('total_countries','DESC');

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 11, 2016 at 9:04

Islam Al-Rayan

3962 silver badges7 bronze badges

Collectives™ on Stack Overflow

Laravel query - order by count

2 Answers 2

Comments

Query Builder:

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Query Builder:

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related