Postgres фильтрует строки, соответствующие сгруппированному внешнему ключу, который включает все значения массива
У меня есть 3 таблицы:
create table events (id serial, ...)
create table devices (id serial, ...)
create table event_devices (event_id int, device_id int, ...)
Допустим, данные в event_devices выглядят так:
event_id | device_id
--------------------
1 | 1
1 | 2
1 | 3
2 | 1
2 | 4
Мне нужно провести поиск по двум случаям:
- фильтровать все события, которые содержат любое устройство в заданном списке, так что
{1, 4} -> (1, 2){1, 2, 3} -> (1, 2)
- фильтровать все события, содержащие все устройства в данном списке, так что
{1, 4} -> (2){1, 2, 3} -> ()
Допустим, данный список вводится как массив целых чисел.
Первый случай довольно прост; Я могу просто использовать «IN»:
with
devices_filter as (
select distinct
event_devices.event_id
from event_devices
where
event_devices.device_id in (select unnest($1::int[]) as device_id)
)
select
events.id as event_id
from events
left outer join devices_filter on
devices_filter.event_id = events.id
where
devices_filter.event_id is not null
Но как мне запросить второй случай? Я подумал, может быть, мне нужен еще один CTE, который группирует и агрегирует идентификаторы устройств на основе идентификатора события, затем выполняет пересечение и проверяет, что полученная длина равна длине входного массива, но я не уверен, как именно это сработает. Я также хотел бы избежать ненужной группировки, поскольку таблица event_devices может быть довольно большой.
Любые подсказки или советы?
Ответы 1
Если вы передаете массив, который не имеет дубликатов, вы можете использовать агрегацию:
select ed.event_id
from event_devices ed
where ed.device_id = any (:array)
group by ed.event_id
having count(*) = cardinality(:array)
Если вам нужно привести значения, то :array действительно $1::int[].