我想选择所有类型为学生的用户，并计算与他们连接的所有国家/地区。该订单应基于与其相关的国家的数量。
用户表：
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
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();
那么您将得到预期的结果：
name nbsp；类型 nbsp nbsp nbsp nbsp；total_countries
user1 nbsp；学生 nbsp；3
user2 nbsp 学生 nbsp 1
下面的查询生成您期望的输出。
查询生成器：
'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');