Laravel - Getting data from few tables (joining and ordering)

Question

I'm creating a ranking of players badges and I'm stuck with db query.

Tables: user(id), club(id), club_user(user_id, club_id), badges(user_id)

I would like to get list of all users from specified club (for example club.id = 1) with amount of badges they have. Results should be ordered by number of badges.

How to create that kind of db query? Is it possible with Eloquent?

Should it be made with db::table and join?

Table user

id|name
1|John
2|Robert
3|Kate


Table club

id|name
1|Sunshine Club
2|Example Club


Table club_user

user_id|club_id
1|1
2|1
3|2


Table bagdes

id|name|user_id|club_id
1|Champion|1|1
2|Some badge|1|1
3|example|2|1
4|Gold Badge|3|2

so if I would like to get ranking of users from club 1, ordered by badge count.

I should get:

name|number of badges
John|2 (badges)
Robert|1 (badge)

Kate is not it this club.

Answer 1

Try this

select user.name ,user.id as userid , (select count(bagdes.id) from 
bagdes     where user_id= userid) 
as total_badges from user inner join club_user on 
user.id = club_user.user_id where club_user.club_id = 1 

You will get your output.

Answer 2

Finally I made it with this DB::table query:

$users = DB::table('users')
            ->select('users.name', DB::raw('count(*) as badges'))
            ->join('badges', 'badges.user_id', '=', 'users.id')
            ->where('badges.club_id', 1)
            ->groupby('users.id')
            ->orderBy('badges', 'DESC')
            ->get();