Postgres: multiple selects that exclude results from previous ones

Question

I have a table of widgets that have a number of connections with potentially overlapping modes. For instance, widget A may have 10 connections, and 5 of those can run in mode A and all 10 can run in mode B. A connection has at least one supported mode. I'm trying to figure out how to design a query that returns a list of devices that support a given number of connections with particular modes.

The simplified schema looks like this:

table widgets
  id
  name

table connections
  id
  widget_id

table modes_connections
  connection_id
  mode_id

table modes
  id
  name

I need to return widget_ids that satisfy filters similar to:

2 connections with mode A AND
2 connections with mode B AND
1 connection with mode C

I can't just join everything together because the first filter for mode A must exclude those results from the other filters, similarly the mode B filter must exclude those results from the mode C filter, etc.

Also, I'm not sure how to prioritize results so connections with the least number of modes have preference. Consider the case where there are 3 connections that support modes A, B, C and two that support mode B. In the filter example above, the mode B filter should select the only-mode-B connections, allowing the A,B,C mode connections to satisfy the requirements for modes A and C.

I'm totally at a dead end. Any suggestions or pointers would be appreciated. Redesigning the schema is also an option.

Answer 1

create table widgets (id int, name text);
create table connections(id int, widget_id int);
create table modes_connections(connection_id int, mode_id int);
create table modes (id int, name text);

insert into widgets values
(1, 'widget1'), (2, 'widget2'),(3, 'widget3'),(4, 'widget4');

insert into connections values
(1, 1),(2, 1),(3, 1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,1),
(11, 2),(12, 2),(13, 3),(14, 3),(15, 4);

insert into modes_connections values
(1, 1),(1, 2),(1, 3),(1, 4),(1, 5),
(2, 1),(2, 2),(2, 3),(2, 4),(2, 5),
(2, 6),(2, 7),(2, 8),(2, 9),(2, 10),
(3, 5),(3, 6),(3, 7),(3, 8),(3, 9);

insert into modes values 
(1, 'A'),(2, 'B'),(3, 'C'),(4, 'D');

select w.id, w.name, m.id, m.name, count(c.id) connections
from   widgets w
join   connections c
on     c.widget_id = w.id
join   modes_connections mc
on     mc.connection_id = c.id
join   modes m
on     m.id = mc.mode_id
group by w.id, w.name, m.id, m.name 
;

id | name    | id | name | connections
-: | :------ | -: | :--- | ----------:
 1 | widget1 |  1 | A    |           2
 1 | widget1 |  2 | B    |           2
 1 | widget1 |  3 | C    |           2
 1 | widget1 |  4 | D    |           2

You can add more conditions to the WHERE clause using the format:

exists (select 1 from x where id = wdg.id and ......)

;with x as
(
select w.id, w.name, m.id mode_id, m.name mode_name, count(c.id) connections
from   widgets w
join   connections c
on     c.widget_id = w.id
join   modes_connections mc
on     mc.connection_id = c.id
join   modes m
on     m.id = mc.mode_id
group by w.id, w.name, m.id, m.name 
)
select id, name
from   widgets wdg
where  exists (select 1 from x where id=wdg.id and mode_id = 1 and connections = 2)
and    exists (select 1 from x where id=wdg.id and mode_id = 2 and connections = 2)

id | name   
-: | :------
 1 | widget1

dbfiddle here

Answer 2

I'm stumped myself. Is this what you're looking for?

select * from (
  select id, name, acnt, bcnt, ccnt,
         row_number()
         over (partition by id order by (acnt+bcnt+ccnt)) RN
    from (
      select w.id, w.name,
        (select count(*)
           from modes m, mode_connections mc,
                connections c
           where m.name = 'A'
             and m.id = mc.mode_id
             and mc.connection_id = c.id
             and c.widget_id = w.id )  ACNT,
        (select count(*)
           from modes m, mode_connections mc,
                connections c
           where m.name = 'B'
             and m.id = mc.mode_id
             and mc.connection_id = c.id
             and c.widget_id = w.id )  BCNT,
        (select count(*)
           from modes m, mode_connections mc,
                connections c
           where m.name = 'C'
             and m.id = mc.mode_id
             and mc.connection_id = c.id
             and c.widget_id = w.id )  CCNT
         from widgets w
    )
    where acnt >= 2 and bcnt >= 2 and ccnt >= 1
) where RN = 1