Ordering an array by the count of a related table result

Question

I'm trying the following:

I have two models: Pub and User, related by an intermediate table called 'recommended_pubs', where a user set a certain pub as "recommended".

The relations are NxN as follows:

Pub:

 /**
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 */
public function recommendedUsers()
{
    return $this->belongsToMany(User::class, 'recommended_pubs');
}

User:

/**
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 */
public function recommendedPubs()
{
    return $this->belongsToMany(Pub::class, 'recommended_pubs');
}

recommended_pubs table has the following fields: id|user_id|pub_id

I would like to receive an array of certain pubs from other function ($pubs) and then order them by the number of times a pub has been recommended by users.

I tried the following and it works:

$pubs = Pub::leftJoin('recommended_pubs', 'recommended_pubs.pub_id', '=', 'pubs.id')
                    ->leftJoin('users', 'recommended_pubs.pub_id', '=', 'users.id')
                    ->groupBy('pubs.id')
                    ->orderBy('users_count','desc')
                    ->selectRaw('pubs.*, count(users.id) as users_count')
                    ->get()
                    ;

Any idea? I tried to do it inside a foreach but I had some problems modifying the leftjoins...

Thanks a lot!!

Answer 1

Use withCount():

Pub::withCount('recommendedUsers')
    ->orderBy('recommended_users_count', 'desc')
    ->get();