PostgreSQL, где все в массиве

Предполагая, что объединенная таблица соответствует хорошей практике и имеет уникальный составной ключ, то есть ограничение для предотвращения дублирования строк, тогда должно выполняться что-то вроде следующего простого запроса.

select conversation_id from conversations_users where user_id in (1, 2)
group by conversation_id having count(*) = 2

Важно отметить, что цифра 2 в конце - это длина списка user_ids. Это, очевидно, нужно изменить, если список user_id изменит длину. Если вы не можете предположить, что ваша объединяемая таблица не содержит дубликатов, измените «count (*)» на «count (independent user_id)» с некоторой возможной ценой в производительности.

Этот запрос находит все беседы, которые включают всех указанных пользователей, даже если беседа также включает дополнительных пользователей.

Если вы хотите общаться только с точно указанным набором пользователей, можно использовать вложенный подзапрос в предложении where, как показано ниже. Обратите внимание: первая и последняя строки совпадают с исходным запросом, только две средние строки являются новыми.

select conversation_id from conversations_users where user_id in (1, 2)
   and conversation_id not in
   (select conversation_id from conversations_users where user_id not in (1,2))
group by conversation_id having count(*) = 2

Точно так же вы можете использовать оператор разницы наборов, если ваша база данных поддерживает его. Вот пример синтаксиса Oracle. (Для Postgres или DB2 измените ключевое слово «минус» на «кроме».)

select conversation_id from conversations_users where user_id in (1, 2)
  group by conversation_id having count(*) = 2
minus
  select conversation_id from conversations_users where user_id not in (1,2)

Хороший оптимизатор запросов должен обрабатывать последние два варианта одинаково, но для уверенности сверьтесь с вашей конкретной базой данных. Например, план запроса Oracle 11GR2 сортирует два набора идентификаторов диалога перед применением оператора минус, но пропускает этап сортировки для последнего запроса. Таким образом, любой план запроса может быть быстрее в зависимости от нескольких факторов, таких как количество строк, ядер, кеша, индексов и т. Д.

Я сворачиваю этих пользователей в массив. Я также использую CTE (вещь в предложении WITH), чтобы сделать это более читаемым.

=> select * from conversations_users ;
 conversation_id | user_id
-----------------+---------
               1 |       1
               1 |       2
               2 |       1
               2 |       3
               3 |       1
               3 |       2
(6 rows)       

=> WITH users_on_conversation AS (
  SELECT conversation_id, array_agg(user_id) as users
  FROM conversations_users
  WHERE user_id in (1, 2) --filter here for performance                                                                                      
  GROUP BY conversation_id
)
SELECT * FROM users_on_conversation
WHERE users @> array[1, 2];
 conversation_id | users
-----------------+-------
               1 | {1,2}
               3 | {1,2}
(2 rows) 

ИЗМЕНИТЬ (Некоторые ресурсы)

• функции массива: http://www.postgresql.org/docs/9.1/static/functions-array.html
• CTE: http://www.postgresql.org/docs/9.1/static/queries-with.html

Это сохраняет ActiveRecord объектов.

В приведенном ниже примере я хочу знать табели учета рабочего времени, связанные со всеми кодами в массиве.

codes = [8,9]

Timesheet.joins(:codes).select('count(*) as count, timesheets.*').
           where('codes.id': codes).
           group('timesheets.id').
           having('count(*) = ?', codes.length)

У вас должны быть полные ActiveRecord объекты для работы. Если вы хотите, чтобы это была настоящая область видимости, вы можете просто использовать приведенный выше пример и передать результаты с помощью .pluck(:id).

Хотя ответ @Alex с IN и count(), вероятно, является самым простым решением, я ожидаю, что эта функция PL / pgSQL будет более быстрой:

CREATE OR REPLACE FUNCTION f_conversations_among_users(_user_arr int[])
  RETURNS SETOF conversations AS
$BODY$
DECLARE
    _sql text := '
    SELECT c.*
    FROM   conversations c';
    i int;
BEGIN

FOREACH i IN ARRAY _user_arr LOOP
    _sql  := _sql  || '
    JOIN   conversations_users x' || i || ' USING (conversation_id)';
END LOOP;

_sql  := _sql  || '
    WHERE  TRUE';

FOREACH i IN ARRAY _user_arr LOOP
    _sql  := _sql  || '
    AND    x' || i || '.user_id = ' || i;
END LOOP;

/* uncomment for conversations with exact list of users and no more
_sql  := _sql  || '
    AND    NOT EXISTS (
        SELECT 1
        FROM   conversations_users u
        WHERE  u.conversation_id = c.conversation_id
        AND    u.user_id <> ALL (_user_arr)
        )
*/

-- RAISE NOTICE '%', _sql;
RETURN QUERY EXECUTE _sql;

END;
$BODY$ LANGUAGE plpgsql VOLATILE;

Вызов:

SELECT * FROM f_conversations_among_users('{1,2}')

Функция динамически строит, выполняет запрос формы:

SELECT c.*
FROM   conversations c
JOIN   conversations_users x1 USING (conversation_id)
JOIN   conversations_users x2 USING (conversation_id)
...
WHERE  TRUE
AND    x1.user_id = 1
AND    x2.user_id = 2
...

Эта форма лучше всего показала себя при обширном тестировании запросов на реляционное разделение.

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

Для быстрого выполнения любого запроса требуется индекс, подобный следующему:

CREATE INDEX conversations_users_user_id_idx ON conversations_users (user_id);

Многостолбцовый первичный (или уникальный) ключ на (user_id, conversation_id) тоже подойдет, но ключ на (conversation_id, user_id) (как и у вас вполне может быть!) Будет второстепенным. Вы найдете краткое обоснование по ссылке выше или исчерпывающую оценку по этому связанному вопросу на dba.SE

Я также предполагаю, что у вас есть первичный ключ на conversations.conversation_id.

Можете ли вы запустить тест производительности с EXPLAIN ANALYZE по запросу @Alex и этой функции и сообщить о своих результатах?

Обратите внимание, что оба решения находят разговоры, в которых принимают участие по крайней мере пользователи в массиве, включая разговоры с дополнительными пользователями.
Если вы хотите исключить их, не комментируйте дополнительное предложение в моей функции ( или добавьте его к любому другому запросу).

Скажите, если вам нужно больше пояснений по функциям функции.

создать таблицу сопоставления со всеми возможными значениями и использовать это

select 
    t1.col from conversations_users as t1 
    inner join mapping_table as map on t1.user_id=map.user_id
group by 
    t1.col  
having  
    count(distinct conversations_users.user_id)=
    (select count(distinct user_id) from mapping)

select id from conversations where not exists(
    select * from conversations_users cu 
    where cu.conversation_id=conversations.id 
    and cu.user_id not in(1,2,3)        
)

это можно легко превратить в рельсовый прицел.

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

Ваш вопрос был неясен, хотите ли вы беседы с точно определенной группой пользователей или беседы с надмножеством. Следующее относится к расширенному набору:

with users as (select user_id from users where user_id in (<list>)
              ),
     conv  as (select conversation_id, user_id
               from conversations_users
               where user_id in (<list>)
              )
select distinct conversation_id
from users u left outer join
     conv c
     on u.user_id = c.user_id
where c.conversation_id is not null

Для правильной работы этого запроса предполагается, что у вас есть индексы для user_id как в users, так и в chatations_users.

Для точного набора. . .

with users as (select user_id from users where user_id in (<list>)
              ),
     conv  as (select conversation_id, user_id
               from conversations_users
               where user_id in (<list>)
              )
select distinct conversation_id
from users u full outer join
     conv c
     on u.user_id = c.user_id
where c.conversation_id is not null and u.user_id is not null

Основываясь на ответе @Alex Blakemore, эквивалентная область видимости Rails 4 для вашего класса Conversation будет:

# Conversations exactly with users array
scope :by_users, -> (users) { 
                           self.by_any_of_users(users)
                             .group("conversations.id")
                             .having("COUNT(*) = ?", users.length) -
                           joins(:conversations_users)
                             .where("conversations_users.user_id NOT IN (?)", users)
}
# generates an IN clause
scope :by_any_of_users, -> (users) { joins(:conversations_users).where(conversations_users: { user_id: users }).distinct }

Обратите внимание, что вы можете оптимизировать его вместо того, чтобы делать Rails - (минус), вы могли бы сделать .where("NOT IN"), но это было бы очень сложно читать.

На основе ответа Алекса Блейкмора

select conversation_id
from conversations_users cu
where user_id in (1, 2)
group by conversation_id 
having count(distinct user_id) = 2

Я нашел альтернативный запрос с той же целью, обнаружив беседу_id беседы, которая содержит user_1 и user_2 (без учета дополнительных пользователей)

select *
from conversations_users cu1
where 2 = (
    select count(distinct user_id)
    from conversations_users cu2
    where user_id in (1, 2) and cu1.conversation_id = cu2.conversation_id
)

Это медленнее согласно анализу, который postgres выполняет с помощью оператора запроса объяснения, и я предполагаю, что это правда, потому что есть больше условий, которые должны быть оценены, по крайней мере, для каждой строки conversations_users подзапрос будет выполняться, поскольку это коррелированный подзапрос. Положительный момент этого запроса заключается в том, что вы не группируете, поэтому вы можете выбрать дополнительные поля в таблице convert_users. В некоторых ситуациях (например, в моей) это может быть удобно.