How to get conversation of given users (using where condition twice on same column)?

Question

I am trying to create a chat app in Laravel. When a conversation is created between users, I don't want to record a user as host user. I would like to get a conversation filtered by given users. Example: If USER_1 and USER_2 belong to CONVERSATION_1, I want to filter out CONVERSATION_1 by USER_1 and USER_2 ids.

Here is my table structures.

conversations

id
product_id

conversation_users

id
conversation_id
user_id

Here is my conversation service.

class ConversationService
{
    public static function first(User $user){
        $admin = getAdmin();
        $conversation = Conversation::whereHas("users", function($q) use($user, $admin){
            $q->where("user_id", $user->id)->where("user_id", $admin->id);
        })->first();
    }
}

I know I can't use this statement. $q->where("user_id", $user->id)->where("user_id", $admin->id);

Could you please guide me how to filter a conversation by user ids?

score 0 · Accepted Answer · answered Jun 23 '22 at 08:59

Since the conversation must belong to both users explicitly, just double on the condition, one for each user

$conversation = Conversation::query()
    ->whereHas("users", function($q) use($admin){
        $q->where("user_id", $admin->id);
    })
    ->whereHas("users", function($q) use($user){
        $q->where("user_id", $user->id);
    })
    ->first();

Once you see this version of the code, it will tick that it's simple logic.

How to get conversation of given users (using where condition twice on same column)?

1 Answers1