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.

• McNets: Could you add sample data and the desired result?

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

• user3298142: Thank you! This helps a lot. I don't think it covers the preference selection though. See this dbfiddle: dbfiddle.uk/… I would expect the last query to return both widget1 and widget2, since both can support that combination of modes.
• McNets: I'm glad to help, please upvote the answer if you like it.
• user3298142: Playing with this a bit, I do see an issue - connections are counted more than once in the filter. Is there an easy way to fix that? Example: dbfiddle.uk/… Here I query on >= 2 mode A and >= 1 mode C and get both widgets. There are connections that satisfy each of those filters (1 and 2 in widget 1), but I'm really looking for independent connections (3 ports total, which none of the widgets satisfy). I couldn't figure out how to ensure the connections "counted" in one filter aren't "counted" in another.
• McNets: I'm sorry, it's not clear to me, do you need connections(A + C) >= 3 or connections(A) = 3 OR connections(B) = 3 or connections(A) >= 2 AND connections(B) >= 3? Could you rewrite the formula?
• user3298142: Again, I really appreciate the help! Connections can only be counted once. If one connection supports modes A or B or C, and another connection only supports B, then a query for 1xA and 2xB should return nothing, since it's not possible for 2 connections to satisfy that constraint. There will probably need to be a sort involved here, since mode filters should be satisfied by the connections with the fewest modes. In this example, 1xB and 1xA works, but only if Conn1=A and Conn2=B, not the other way around.

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