Postgres filter rows matching grouped foreign key that includes all values of an array

Question

I've got 3 tables:

create table events (id serial, ...)

create table devices (id serial, ...)

create table event_devices (event_id int, device_id int, ...)

Let's say the data in event_devices looks like this:

event_id | device_id
--------------------
1        | 1
1        | 2
1        | 3
2        | 1
2        | 4

I need to conduct a search for two cases:

filter all events that contain any device in a given list such that

{1, 4} -> (1, 2)
{1, 2, 3} -> (1, 2)

filter all events that contain all devices in a given list such that

{1, 4} -> (2)
{1, 2, 3} -> ()

Let's say the given list is input as an array of ints.

The first case is pretty simple; I can simply use "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

But how do I query for the second case? I've thought may be I need another CTE that groups and aggregates device ids based on event id, then perform an intersection, and check that the resulting length is equal to the length of the input array, but I'm not sure exactly how that would work. I'd also like to avoid any unnecessary grouping, since the event_devices table can be quite large.

Any hints or tips?

Gordon Linoff · Accepted Answer · 2020-12-23 18:17:52Z

1

If you are passing in an array that has no duplicates, you can use aggregation:

select ed.event_id
from event_devices ed
where ed.device_id = any (:array)
group by ed.event_id
having count(*) = cardinality(:array)

If you need to cast the values, then :array is really $1::int[].

answered Dec 23, 2020 at 18:17

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres filter rows matching grouped foreign key that includes all values of an array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related