Ehsan Ehsan - 1 month ago 7
MySQL Question

Laravel Eloquent get multiple record with same foreign key

I have 3 tables:

User
------------------
ID
Name


Room
------------------
ID
Subject



Participate
-------------------
ID
room_id
user_id


and it's my sample data:

user room participate
----------------- ---------------- ---------------------
ID Name ID subject ID room_id user_id
----------------- ---------------- ---------------------
1 john 1 room1 1 1 1
2 sara 2 room2 2 1 2
3 david 3 room3 3 3 3
4 3 2
5 3 1
6 2 1


Now I access to
user_id
but I want to find
room_id
that have shared with user_IDs in
participate
table. For example I have
user_id: 1
&
user_id: 2
and want to get shared room_id
1
. Already I filter participate model by
user_id
but I don't know to find room_id.

Thanks

Answer

You can try to create such a query:

$usersIDs = [$userId, $secondUserId]; //here define user ids how many  you want

DB::table('room')->select('room.*')
    ->join('participate', 'participate.room_id', '=', 'room.id')
    ->join('user', 'user.id', '=', 'participate.user_id')
    ->whereIn('user.id', $usersIDs)
    ->havingRaw('count(user.id) = ?', [count($usersIDs)])
    ->groupBy('room.id')
    ->first();