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!!

score 0 · Accepted Answer · answered Jun 06 '18 at 16:40

0

Use withCount():

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

answered Jun 06 '18 at 16:40

Jonas Staudenmeir

24,815
6
63
109

Thanks a lot ofr answering!, but how do I use it with an array? $pub = [$pub1, ....... ,$pubn] – Ber Tsacianegu del Tepuy Jun 07 '18 at 08:33
Do you mean `$pubs = Pub::withCount()...`? – Jonas Staudenmeir Jun 07 '18 at 12:20
Finally I did this: $pub->withCount('recommendedUsers') ->orderBy('recommended_users_count', 'desc') ->get(); Thanks a lot for your answer!! – Ber Tsacianegu del Tepuy Jun 07 '18 at 13:36

Ordering an array by the count of a related table result

1 Answers1