0

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.

  • Could you add sample data and the desired result? – McNets Feb 5 '18 at 21:33
0
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

  • 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. – user3298142 Feb 5 '18 at 22:54
  • I'm glad to help, please upvote the answer if you like it. – McNets Feb 5 '18 at 23:02
  • 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. – user3298142 Feb 5 '18 at 23:45
  • 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? – McNets Feb 6 '18 at 7:58
  • 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. – user3298142 Feb 11 '18 at 22:29
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.