使用whereHas与having count无法匹配正确会话数据的问题排查

我有users、conversations、conversation_user和messages四张表，在根据用户ID数组$data创建新会话前，想先查找是否存在完全匹配的会话。当前代码存在两个问题：

• 第一个whereHas会返回不符合要求的会话（比如只包含当前用户ID1和用户ID2的会话），因为只要有一个用户ID在$data里就会被匹配。
• 第二个whereHas用来统计会话用户总数，但仅按conversation_id分组会触发MySQL SQL_MODE分组错误，加上conversation_user.id后又查不到匹配结果。

原查询代码 #

$data = [2,3]; // 不含当前用户ID（auth()->id()为1），目标会话总用户数为3

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); // 总数量为3
})->first()

生成的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

表结构 #

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

可行参考代码 #

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()

内容的提问来源于stack exchange，提问作者SOIX