php - Laravel 查询 - 按计数排序

Question

我想选择所有类型为student 的用户，并计算与他们相关的所有国家/地区。该顺序应基于与其关联的国家/地区数量。

用户表:

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

国家表:

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

pivot_countries_user 表:

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表:

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

这是我尝试过的 laravel 查询:

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

预期输出:

name      type       total_countries
user1     student    3
user2     student    1

Answer 1

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();

那么你会得到预期的结果:
名称 类型 total_countries
user1 学生 3
user2 学生 1