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.