CODEWITHSUNDEEP
phpmysqllaraveleloquent
moonvader
moonvader

Reputation: 21091

Optimal way to find intersection in Laravel

I have User and Chat model in Laravel app. User can be member in many chats so I have many-to-many relation between chats and users - belongsToMany is used.

Let's say I know few user IDs and I want to retrieve all common chats (chats where they are members at the same time, e.g., group chats where all the users with the given IDs are grouped in). How this query should be build using Laravel?

Upvotes: 5

Views: 600

Answers (2)

apokryfos
apokryfos

Reputation: 40681

The most optimal way would be to do a join:

Chat::join('chat_user', 'chat_id', 'chats.id')
   ->whereIn('chat_user.user_id',$listOfUserIds)
   ->selectRaw('chat_user.chat_id, COUNT(1) as users_count')
   ->groupBy('chat_user.chat_id')
   ->having('users_count','=', count($listOfUserIds));

This is optimal because it only joins the pivot.

The elegant way to do this though is to use whereHas with the "cardinality" parameters, i.e., find all chats which those users are involved in as long as all of them are involved.

Chat::whereHas('user', function ($q) {
    return $q->whereIn('id', $listOfUserIds);
}, '=', count($listOfUserIds));

this still only does one query but joins the pivot and the users table.

Upvotes: 2

Christophe Hubert
Christophe Hubert

Reputation: 2951

You can use:

    $membersWithChats = Member::whereIn('id', $listOfIDs)->with('chats')->get();

Upvotes: 1

Related Questions