Correctly Query a Pivot Table

To check if a conversation exists

$first_user->conversations()->wherePivot('user_id ', $second_user)->exists();

And to get the id of the conversation

$first_user->conversations()->wherePivot('user_id ', $second_user)->first()->id;

for more info read about Filtering Relationships Via Intermediate Table Columns

Hi @ahmeddabak Thanks so much for the quick response! I tried using

return $user->conversations()->wherePivot('user_id ', $organizer->user->id)->exists();

and I get the error

Unknown column 'conversation_user.user_id ' in 'where clause' (SQL: select exists(select * from `conversations` inner join `conversation_user` on `conversations`.`id` = `conversation_user`.`conversation_id` where `conversation_user`.`user_id` = 2 and `conversation_user`.`user_id ` = 1) as `exists`)"

I can look at my database and see that in my conversation_user table I have a user_id column. Currently the table has

id     conversation_id    user_id  
1             1                            1
2             1                            2

Am I doing something wrong?

Edited

There a many ways to do this:

• Check the intersection between 2 Eloquent query results. The intersection between: App\User::find(1)->conversations->pluck('conversations.id') and App\User::find(2)->conversations->pluck('conversations.id). Here, array_intersect might help.
• Give some sort of name to a conversation to identify it easily such as: 1_2 (starting with the smallest user id number). Information is duplicated but the query for an existing conversation between 2 users is made simple and straightforward.
• Use the SQL way with query builders, here are 2 suggestions:

Option 1:

DB::table(function($q) {
            $q->select('conversation_user.conversation_id')->from('conversation_user')->where('conversation_user.user_id',1);
        },'authconversations')->join('conversation_user','authconversations.conversation_id','=','conversation_user.conversation_id')->where('conversation_user.user_id',2)->exists();

Here, I am using a sub query to retrieve all conversations for user 1. I assumed user 1 is the authenticated user, hence the naming authconversations, it could be changed. Then, I joined the result with the pivot table and restricted it to user 2 conversations.

Or to put it simply, we store user 1 conversations and perform a query on the result to check if one is matching with user 2.

I used exists at the end to shorten the query but it could be done with whereExists.

Option 2a:

DB::table('conversation_user as a')
            ->join('conversation_user as b', 'a.id', '!=','b.id')
            ->where([['a.user_id','=',1],['b.user_id','=',2]])
            ->whereColumn('a.conversation_id','b.conversation_id')
            ->exists();

Here, it is a self join. Think of a list of pivot entries 1,2,3,4 but associated with one another: 1-2, 1-3, 2-1. != prevents from having 1-1. If we find an entry with user id 1, user id 2, and the same conversation id, then there is a conversation between the users 1 and 2. Why do we bother checking that it is the same conversation id ? Well, the self join also joins unrelated conversations.

Option 2b:

DB::table('conversation_user as a')
            ->join('conversation_user as b', 'a.id', '<','b.id')
            ->where(function($q){
                $q->where([['a.user_id','=',1],['b.user_id','=',2]])
                ->orWhere([['a.user_id','=',2],['b.user_id','=',1]]);
            })
            ->whereColumn('a.conversation_id','b.conversation_id')
            ->exists();

Here, the join is more restrictive, < is used instead of !=. We get combinations instead of permutations. The benefit is that the join gets less results. If I have 1 - 2, I don't need 2 - 1. As a result, we use orWhere to check for either orders. Is it more performant than 2a ? Maybe. Even if we do add an 'orWhere', it is applied on a smaller data set. Performance tests would help to compare both options.

Hello @chrisgrim

why you are searching it from pivot table you just make this following query

$conversation = App\User::find($organizer->user->id)->Conversation()->get();

dd($conversation->toArray());

your User model must have this

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

and you will get this following result

array:2 [▼
  0 => array:5 [▼
    "id" => 1
    "body" => "edsdsfsfsf"
    "created_at" => null
    "updated_at" => null
    "pivot" => array:2 [▼
      "user_id" => 1
      "conversation_id" => 1
    ]
  ]
  1 => array:5 [▼
    "id" => 1
    "body" => "edsdsfsfsf"
    "created_at" => null
    "updated_at" => null
    "pivot" => array:2 [▼
      "user_id" => 1
      "conversation_id" => 1
    ]
  ]
]

@a4ashraf I don't see how the current user is being used to filter at all? I only want to see the conversation between the current user and the organizer->user

@ismaile I don't understand the authconversations part. Is it possible to do it without that?

@chrisgrim

you mean you have two fields in pivot table (User_id and orgnizer_id)

if yes then the query will hardcoded in User model,

the better solution is that we add orgnizer_id in Conversation table and use Polymorphic Relationships

@chrisgrim

Well, you can call it: first_user_conversations_id instead of authconversations. I don't know of any easy Laravel way to do it at this time. I don't know of any package for this either.

It's a bit hard to explain without a drawing. Let's imagine, you have the pivot table with all conversations for user 1 and user 2. So, it would be like:

conversation_id                              user_id
1                                            | 1
16                                           | 2
12                                           | 1
17                                           | 1
?                                            | 2
?                                            | 1
?                                            | 1
... 100 more

How do we know if user 1 and user 2 had a conversation ? Well, we should compare 2 rows. For line 1, we have conversation_id 1 for user_id 1 , so is there a conversation_id 1 with user_id 2 ? But, if we don't find any, we are not done because, maybe their conversation id was not 1.

Anyway, a simple way to deal with this is to reduce the conversation ids to the ones of user 1. That's why, I took conversations ids for user 1. Then, I performed a query on these conversations looking for user 2.

Imagine if I filtered the pivot table to user 1 conversations only. I wouldn't have any user 2 entry. That's the reason why I looked for conversation ids for user 1 and not pivot table entries for user 1.

Hope this clarifies. It is not very easy to explain.

I think I understand more. I guess I was hoping for a cleaner way to do this like @ahmeddabak provided

Edited

For future references, here is the SQL query associated with the Laravel query - Option 1 mentioned above:

select * from (select `conversation_user`.`conversation_id` from `conversation_user` where `conversation_user`.`user_id` = 1) as `authconversations` inner join `conversation_user` on `authconversations`.`conversation_id` = `conversation_user`.`conversation_id` where `conversation_user`.`user_id` = 2"

And here, it is for the Option 2a:

select * from `conversation_user` as `a` inner join `conversation_user` as `b` on `a`.`id` != `b`.`id` where (`a`.`user_id` = 1 and `b`.`user_id` = 2) and `a`.`conversation_id` = `b`.`conversation_id`

@chrisgrim I have added another query builder option to the initial answer. There are 2 variants but if you don't want to bother, you can stick to the first one: Option 2a.

Thanks so much @ismaile You have put me on the right track!!