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

Ответ 1

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

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 (different user_id)" при некоторой возможной стоимости в производительности.

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

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

select conversation_id from conversations_users where user_id in (1, 2)
   and conversation_id not in
   (select conversation_id from conversation_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 conversation_users where user_id not in (1,2)

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

Ответ 2

Я сворачиваю этих пользователей в массив. Я также использую 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) 

EDIT (некоторые ресурсы)

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

Ответ 3

В то время как ответ @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
...

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

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

Для любого запроса требуется index, как показано ниже:

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 on @Alex 'и эту функцию и сообщить о своих результатах?

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

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

Ответ 4

Это сохраняет объекты 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).

Ответ 5

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

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)

Ответ 6

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

это легко можно сделать в области рельсов.

Ответ 7

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

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

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 как у пользователей, так и у пользователей conversations_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

Ответ 8

На основе ответа @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"), но это было бы очень сложно прочитать.