In my laravel project I have tables shown as below.
groups and users are in Many-to-Many relationship, which mean that a user can belongs to multiple groups.
The group_user table is intermediate table which represents which user belongs to which group.
slot_votes table records users' vote on slot. Relationship of it with groups table is groups table hasMany slot_votes.
groups table
|id | channel_id |
+---+---------+
|1 | U124 |
|2 | U123 |
|3 | U125 |
|4 | U127 |
users table
|id | name |
+---+---------+
|1 | A |
|2 | B |
|3 | C |
|4 | D |
group_user table
|id | group_id | user_id |
+---+----------+-----------+
|1 | 1 | 1 |
|2 | 2 | 1 |
|3 | 2 | 2 |
|4 | 3 | 3 |
|5 | 4 | 3 |
|6 | 1 | 4 |
slot_vote table
|id | user_id | group_id | slot |
+---+---------+-----------+-----------+
|1 | 1 | 1 | A |
|2 | 2 | 1 | B |
|3 | 2 | 2 | C |
|1 | 1 | 4 | C |
My question is how can I use Laravel Eloquent to get the group ids which has all its members voted?
For example, if group 1's members is $userId = [1, 4] and both of user 1 and 4 has voted (therefore exists in slot_votes table), I want to get group 1 id in the result.
What I have tried:
Group::whereHas('users', function ($query, $votedUser) {
$query->whereIn(
'id',
SlotVote::where('group_id', $groupId)->pluck('user_id')->unique()
);
}, '=', count($votedUser))->get();
But I couldn't figure out how to get $groupId in whereIn clause. Also, I am concerned of the query performance. Any idea on how can I get the results I need?
Thank you in advanced.
I managed to solve it using the following code:
Group::leftJoin('group_user', 'groups.id', '=', 'group_user.group_id')
->leftJoin('slot_votes', 'group_user.user_id', '=', 'slot_votes.user_id')
->selectRaw(
"groups.id, channel_id,
count(distinct group_user.user_id) as members,
count(distinct slot_votes.user_id) as voted_members",
)
->groupBy('groups.id')
->havingRaw('members=voted_members')
->get();