How to query pivot table to get users sharing same parent

Let's say we have a groups of users, and we want select users belonging to same group. But not only that, we want to select a groups where we have only users by given ID's and no one else.

In our case we have chat groups and and we do not want to create duplicate chat groups. For example if user create one on one chat with another user,  and if try to that again we have to check do we already have that chat group and redirect user to it and not create a new one.

Our chat db structure is like this:

As you can see in our structure, user can have one on one chat, but they can also participate in a group chat. In chat No 3, we have all 3 users, but we want to select only group where we have two specific users.

So, how to check do we already have a one to one chat room created for Peter and Steven?
Hmm...

So i guess first step would be to selected all chat groups where we have user_id = 2 (Peter) and user_id = 3 (Steven).

Let's do that:

                    $user_ids = [2,3];
$chat = $this->chatRepository->chat

    ->whereHas('users',function($query) use($user_ids){

        $query->where('users.id',$user_ids[0]);

    })

    ->whereHas('users',function($query) use($user_ids){

        $query->where('users.id',$user_ids[1]);

    })
->get();
                  

By executing this query we will get chat room with id = 2 and 3 - because they both are inside. But in chat room 3 we have 3 users, so to get our one to one chat we have to select chat with only two users in total.

Now we have to filter it further to say, give as only chat rooms where we have 2 users and id's of the users are 2 and 3:

                    $user_ids = [2,3];
$chat = $this->chatRepository->chat
    ->whereHas('users',function($query) use($user_ids){
        $query->where('users.id',$user_ids[0]);
    })
    ->whereHas('users',function($query) use($user_ids){
        $query->where('users.id',$user_ids[1]);
    })
    ->with(['users.userDetail']) // join users with user details
    ->withCount(['users'])
    //->having('users_count', '=', 2)// this can be used also instead of has
    ->has('users', '=', 2)
    ->orderBy('updated_at', 'DESC')
    ->get();
                  

By executing this query we will get our chat room with our two users.

fabio-unsplash