CODEWITHSUNDEEP
databaselaraveleloquent
Savlon
Savlon

Reputation: 789

How to find multiple values in database and return common ID (Many to Many)

I'm developing a private chat application that has tables setup as per the image below.

Tables

To support group chat, I've made it so there's a Many to Many relationship between conversations and users (conversation_user). This allows me to link/add users to conversations without restricting it to only two users per conversation (sender_id, recipient_id etc).

In order to create a conversation, there MUST be a sender and a recipient. After the conversation has been initialised with these two users, more users can be added to the conversation.

My question

How can I check if there is an existing conversation which involves the sender AND recipient.

What I've tried

This is clearly the wrong way to go about it because it doesn't give me the desired results, but I'm trying to find any instance where the sender and receiver ID's I pass in share the same conversation ID. NOTE: my 'conversation_user' table is named 'participants' in the Conversation Model. I would also prefer to retrieve the relevant details from the conversation, not the users, which I already know how to do.

$existingConversation = Conversation::whereHas('participants')->with(['participants' => function($query) use ($request) {
            $query->where('user_id', $request->recipient_id);
        }])
        ->whereHas('participants')->with(['participants' => function($query) use ($request) {
            $query->where('user_id', $request->sender_id);
        }])->groupBy('id')->get();

Example scenario

I have the below entries in my database table 'conversation_user'. Database Table Rows

The backend receives sender_id: 1 and recipient_id: 3 from the front end. A query is executed to find ANY conversations which involve the sender and the receiver. In this instance, it should return the joining conversation 33.

Upvotes: 0

Views: 468

Answers (2)

Helioarch
Helioarch

Reputation: 1188

This is not that much more elegant but i think will help you with having multiple ids.

// assuming $request->participant_ids will be your array of ids

$existingConversation = Conversation::with('participants');

foreach($request->participant_ids as $participant_id){
    $existingConversation->whereHas('participants', function($query) use ($participant_id){
        $query->where('user_id', $participant_id);
    });
}

$existingConversation = $existingConversation->get();

Edit:

If what you mean elegant is only in terms of the syntax i also would suggest trying to use local scope

// in Conversation model
public function scopeHasParticipants($query, Array $participantArr){
    foreach($participantArr as $participant_id){
        $query->whereHas('participants', function($query) use ($participant_id){
            $query->where('user_id', $participant_id);
        });
    }

    return $query;
}

Then you can use it like so:

// still assuming $request->participant_ids will be your array of ids
$existingConversation = Conversation::with('participants')->hasParticipants($request->participant_ids)->get();

Upvotes: 1

Savlon
Savlon

Reputation: 789

I managed to figure out a non-elegant way, so I'm still happy to see what anyone else comes up with. I basically perform one query to find all conversations with the sender_id and then perform a separate query on that data to find all instances where the recipient_id is found. Seems rather hackish to me... but it works I guess. It'll definitely become a problem if I want to search for existing conversations containing MORE than two users.

$existingConversation = Conversation::with('participants')->whereHas('participants', function($query) use ($request){
    $query->where('user_id', $request->sender_id);
});

$existingConversation = $existingConversation->with('participants')->whereHas('participants', function($query) use ($request){
    $query->where('user_id', $request->recipient_id);
})->get();

Upvotes: 0

Related Questions