Laravel Eloquent: Get record that contains two specific relations

Question

I have a project where a user can create conversations with other users. A conversation can belongsToMany users and user can belongsToMany conversations.

I now need to get the conversation in which two specific users participate.

I tried a combination of solutions using whereIn and I tried the following:

$c = Conversation::whereHas('users', function($q)
     {
         $q->whereIn('user_id', array(1,3));
     })
    ->get();

Here the problem is that whereIn('user_id', [1,3]) gets records that contains EITHER 1 or 3. I need it to return records that contains BOTH 1 and 3.

Conversation Model

class Conversation extends Model {

    public function users(){
        return $this->belongsToMany('App\User');
    }
}

User Model

class User extends Model {

    public function conversations(){
        return $this->belongsToMany('App\Conversation');
    }

}

Tables

conversations:

id | subject

conversation_user:

id | user_id | conversation_id

Data from table conversation_user

enter image description here

Are you sure users 1 and 3 didn't belong to every conversation you had? What you already tried looks right to me. — user1669496, May 12 '15 at 13:34
yes, I included a screenshot of the current data in the table for clarity. — Nicklas Kevin Frank, May 12 '15 at 13:37

user1669496 · Accepted Answer · 2015-05-12T13:59:12.817

Your newest edit makes a lot more sense, this is actually a very easy fix. whereHas takes two additional parameters where it's going to look for the count.

$users = [1, 3];   

$c = Conversation::whereHas('users', function($q) use ($users)
{
    $q->whereIn('user_id', $users);
}, '>', count($users) )
->get();

This will get all conversations where user's 1 and 3 have participated in, even if there are additional users that have participated in those conversations. If you want only the conversations with only users 1 and 3, change the > to an =.

Edit: I just realized your pivot table has an id column. This method may not work if your pivot table is going to have duplicates. For example, if you have user_id of 1 in there twice with the same conversation_id both times, it will return that conversation even though it technically only has 1 user. I suggest removing the id column and creating a composite primary key of user_id and conversation_id. If there is the possibility of duplicates, it might be safer to use lukasgeiter's solution.

score 2 · Answer 2 · answered May 12 '15 at 13:50

You are currently querying conversations which either user 1 and/or 3 takes part in. To achieve what you want you need two whereHas calls:

$c = Conversation::whereHas('users', function($q)
     {
         $q->where('user_id', 1);
     })
     ->whereHas('users', function($q)
     {
         $q->where('user_id', 3);
     }
     ->get();

And if you have more than two users, add them in a loop:

$users = [1, 2, 3, 4, 5];
$c = Conversation::query();
foreach($users as $userId){
    $c->whereHas('users', function($q) use ($userId)
    {
        $q->where('user_id', $userId);
    });
}
$c = $c->get();

score 0 · Answer 3 · answered May 12 '15 at 13:39

0

I hope this will help you...

    $userIds = array(1,3);
    $c = Conversation::whereHas('users', function($q) use ($userIds)
         {
             $q->whereIn('user_id', $userIds);
         })
        ->get();

answered May 12 '15 at 13:39

NULL

1,848
1
21
23

Appreciated, don't worry I only hardcoded the id's to reduce excess code in the question and to help identify the cause of my problem. This will not solve my problem. – Nicklas Kevin Frank May 12 '15 at 13:41

Laravel Eloquent: Get record that contains two specific relations

3 Answers3