Not able to find correct data with whereHas and having count-CodePudding

I have users, conversations, conversation_user and messages table: Before making a new conversation with $data array of user id's, I am trying to find existing one:


$data = [2,3]; // without auth()->id() -> 1 => total of 3 users per conversation

auth()->user()->conversations()->has('messages')->whereHas('users', function ($query) use ($data) {
    $query->whereIn('user_id', $data);
})->whereHas('users', function ($query) use ($data) {
    $query->groupBy('conversation_id', 'conversation_user.id')
          ->havingRaw('count(conversation_id) = ' . count($data)   1); // total count is 3
})->first()

Now first whereHas returns even if I have conversation between auth()->id() and ID-2, because user_id 2 is in (2,3).. So it would retrieve the wrong conversation, where I need to count for users per conversation as well.

The second whereHas is for counting however if I use $query->groupBy('conversation_id') I get mysql SQL_MODE error for grouping, meaning I need to add $query->groupBy('conversation_id', 'conversation_user.id') as well, but with all that I get no record from database even if there are some.

What am I missing here?

[Updated with generated sql]

select * from `conversations` 
inner join `conversation_user` on `conversations`.`id` = `conversation_user`.`conversation_id`
where `conversation_user`.`user_id` = 1 and exists (
            select * from `conversation_messages` 
            where `conversations`.`id` = `conversation_messages`.`conversation_id` 
            and `conversation_messages`.`deleted_at` is null
) and exists (
            select * from `users` 
            inner join `conversation_user` on `users`.`id` = `conversation_user`.`user_id` 
            where `conversations`.`id` = `conversation_user`.`conversation_id` 
            and `user_id` in (2, 3) and `users`.`deleted_at` is null
) and exists (
            select * from `users` 
            inner join `conversation_user` on `users`.`id` = `conversation_user`.`user_id` 
            where `conversations`.`id` = `conversation_user`.`conversation_id` 
            and `users`.`deleted_at` is null 
            group by `conversation_id`, `conversation_user`.`id` 
            having count(conversation_id) = 3
) and `conversations`.`deleted_at` is null

[Update with table structures]

users -> id, name, email
conversations -> id, slug, subject
conversation_user -> id, user_id, conversation_id
messages -> id, conversation_id, user_id, body

[Another update]

Seems like this works also, in case someone need:

auth()->user()->conversations()->has('messages')->whereHas('users', function ($query) use ($data) {
    $query->whereIn('user_id', $data);
})->whereDoesntHave('users', function ($query) use ($data) {
    $query->whereNotIn('user_id', $data);
})->first()

CodePudding user response：

I think this is the SQL you want -

SELECT c.*
FROM conversation_user cu
JOIN conversations c
    ON cu.conversation_id = c.id
WHERE cu.user_id IN (1, 2, 3)
AND NOT EXISTS (
    SELECT *
    FROM conversation_user
    WHERE conversation_id = cu.conversation_id
    AND user_id NOT IN (1, 2, 3)
)
GROUP BY cu.conversation_id
HAVING COUNT(DISTINCT cu.user_id) = 3

Not sure if this is correct as I am not a Laravel user -

$data = [1, 2, 3];

$conv = DB::table('conversation_user cu')
    ->select('c.*')
    ->join('conversations c', 'cu.conversation_id', '=', 'c.id')
    ->whereIn('cu.user_id', $data)
    ->whereNotExists(function($query) use ($data) {
        $query->select(DB::raw(1))
            ->from('conversation_user')
            ->whereColumn('conversation_id', 'cu.conversation_id')
            ->whereNotIn('user_id', $data);
    })
    ->groupBy('cu.conversation_id')
    ->havingRaw('COUNT(DISTINCT cu.user_id) = ?', count($data))
    ->get();