Laravel, where, orwhere in main table an pivot table

Question

I have two tables with belongsToMany relation: message_topics and users

The pivot table is message_topics_users and contains 2 columns: message_id and user_id.

In table message_topics, I have a field called sender_id

I'm trying to write the correct eloquent syntax to get all the records:

where message_topics.sender_id = $user_id OR Message_topics_users.receiver_id = $user_id

I tried many things, like for instance:

$topics = MessageTopic::where('sender_id', $user_id)
    ->wherePivot('receiver_id', $user_id)->orderBy('sent_at','desc')->get();

Any idea?

Answer 1

You can use the whereHas method (or in this case the orWhereHas method):

$topics = MessageTopic::where('sender_id', $user_id)
    ->orWhereHas('users', function ($query) use ($user_id) {
        $query->where('id', $user_id);
    })
    ->orderBy('sent_at', 'desc')
    ->get();

Answer 2

I'm assuming you have two relationships from the topics? Since it's too arbitrary to use both columns and the same relationship... Like this

//On your MessageTopic model
public function sender(){
    return $this->belongsToMany('App\User', 'message_topics_users', 'message_id', 'sender_id');
}

public function receiver(){
    return $this->belongsToMany('App\User', 'message_topics_users', 'message_id', 'receiver_id'));
}

Then you can use whereHas and orWhereHas like this:

//Again assuming you have your User model loaded as $user
$topics = App\Topic::whereHas('sender', function($q) use($user){
    $q->where('sender_id', '=', $user->id);
})
->orWhereHas('receiver', function($q) use($user){
   $q->where('receiver_id', '=', $user->id
})
->orderByDesc('sent_at')
->get();

whereHas and orWhereHas both query the model (MessageTopic in this case) checking for the existence of the specified relationship (App\Topic::whereHas('sender')...). They also allow you to pass the constraint that you're looking for (function($q) use($user){ $q->... })

So it is basically saying "Give me ONLY the MessageTopics that have a Sender or Receiver with the id $user->id"