Блог

У меня есть три таблицы:

Пользователи:

 id       username
-----------------
1        user1                    
2        user2                    
3        user3
  

чаты:

 id      created_on
---------------------------
1       2020-11-07 00:00:00
2       2020-11-08 00:00:00
  

chat_users (сводная таблица):

 id      chat_id      user_id
----------------------------
1       1            1
2       1            2
3       2            1
4       2            2
5       2            3
  

Учитывая массив идентификаторов пользователей, я хочу вернуть одну chat запись или пустой результат.
Примеры:

1. Учитывая идентификаторы пользователей (1, 2), верните строку чата с идентификатором 1.
2. Учитывая идентификаторы пользователей (1, 3), верните пустой результирующий набор.
3. Учитывая идентификаторы пользователей (1, 2, 3), верните строку чата с идентификатором 2.

Я пробовал что-то вроде:

 select distinct c.*
from chats as c
  join chat_users as cu on cu.user_id in (...)
;
  

но я знаю, что это неправильно, и это не работает для точного сопоставления сводных записей.

Заранее благодарю вас — любая помощь очень ценится, я рисую пробел.

Обновить

Кажется, это работает, но у меня такое чувство, что это не идеальное решение:

 with filtered_chat_users as (
    select cu.chat_id, string_agg(cu.user_id::text, ',' order by cu.user_id asc) as user_ids
    from chat_users as cu 
    where cu.user_id in (1, 2, 3)
    group by cu.chat_id
)
select c.*
from filtered_chat_users as fcu
    join chats as c on c.id = fcu.chat_id
where fcu.user_ids = '1,2,3';
  

UDPATE 2:
Вышеприведенное работает не так, как я думал / надеялся. Передача подмножества пользовательских идентификаторов неправильно совпадает с записями чата: передача (1, 2) возвращает оба чата 1 и 2.

ОБНОВЛЕНИЕ 3:

Это работает, но снова интересно, есть ли более эффективное решение:

 with filtered_chats as (
    select distinct c.id
    from chats as c
        join chat_users as cu on cu.chat_id = c.id
            and cu.user_id in (1, 2)
), filtered_chat_users as (
    select cu.chat_id, string_agg(cu.user_id::text, ',' order by cu.user_id asc) as user_ids
    from chat_users as cu
        join filtered_chats as fc on fc.id = cu.chat_id
    group by cu.chat_id
)
select c.*
from filtered_chat_users as fcu
    join chats as c on c.id = fcu.chat_id
where fcu.user_ids = '1,2'