Laravel Eloquent：获取包含两个特定关系的记录

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:

我尝试了使用whereIn的解决方案组合,我尝试了以下方法:

$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.

这里的问题是whereIn('user_id',[1,3])获取包含EITHER 1或3的记录。我需要它来返回包含BOTH 1和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

id |学科

conversation_user:

id | user_id | conversation_id

id | user_id | conversation_id

Data from table conversation_user

表conversation_user中的数据

3 个解决方案

#1

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.

您的最新编辑更有意义,这实际上是一个非常简单的修复。 whereHas需要两个额外的参数来查找计数。

$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 =.

这将获得用户1和3参与的所有对话,即使有其他用户参与了这些对话。如果您只想要仅与用户1和3进行对话,请将>更改为=。

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.

编辑:我刚刚意识到您的数据透视表有一个id列。如果数据透视表将具有重复项,则此方法可能无效。例如,如果两次使用相同的conversation_id两次,其中user_id为1,则即使技术上只有1个用户,也会返回该对话。我建议删除id列并创建user_id和conversation_id的复合主键。如果有可能重复,使用lukasgeiter的解决方案可能更安全。

#2

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:

您当前正在查询用户1和/或3参与的对话。要实现您想要的目标,您需要两个whereHas调用:

$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();

#3

I hope this will help you...

我希望这能帮到您...

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