php - Sql query to show count(*) of groups while keeping all rows of the group -

i have table similar following

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 

i search sql query (prefer fluent or eloquent laravel 4) give me following result:

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 

as can observed family_members count of users have same father_id

select id, user_id, father_id, count(*) family_members users group father_id 

the query above, keeps top row of each group, want keep other records , not first one; sorting them first according family_members , according father_id

how can achieve it?

i don´t know fluent, nor eloquent, nor laravel 4, sql query this.

 select yourtable.*, auxtable.family_members      yourtable     left join     (         select father_id, count(id) family_members         yourtable         group father_id     )auxtable on yourtable.father_id = auxtable.father_id order family_members desc, yourtable.father_id asc