我有一个类似于以下的表
id user_id father_id
1 1 1
2 2 1
3 3 2
4 4 2
5 5 2
6 6 3
7 7 4
我搜索一个sql查询(Laravel 4更喜欢Fluent或Eloquent),得到以下结果:
id user_id father_id family_members
3 3 2 3
4 4 2 3
5 5 2 3
1 1 1 2
2 2 1 2
6 6 3 1
7 7 4 1
可以看出,family_members
是具有相同father_id
的用户的计数
select id, user_id, father_id, count(*) as family_members from users group by father_id
上面的查询只保留了每组的最上面一行,但我想保留所有其他记录,而不仅仅是第一条;然后首先根据family_members
然后根据father_id
对它们进行排序
我怎样才能做到这一点?
我不知道Fluent、Eloquent和Laravel 4,但sql查询应该是这样的。
SELECT yourTable.*, auxTable.family_members
FROM yourTable
LEFT JOIN
(
SELECT father_id, COUNT(id) as family_members
FROM yourTable
GROUP BY father_id
)auxTable ON yourTable.father_id = auxTable.father_id
ORDER BY family_members DESC, yourTable.father_id ASC
我根据segio0983 的答案通过以下Fluent查询解决了这个问题
$users = DB::table('users')
->leftjoin(DB::raw('(SELECT father_id, COUNT(id) as family_members
FROM users
GROUP BY father_id) auxTable '),function($join)
{
$join->on('users.father_id', '=', 'auxTable.father_id');
})
->orderBy('auxTable.family_members','desc')
->orderBy('users.father_id','asc')
->select('users.id','users.father_id','auxTable.family_members');