SqlAlchemy: filter to match all instead of any values in list?

Question

I want to query a junction table for the value of column aID that matches all values of a list of ids ids=[3,5] in column bID.

This is my junction table (JT):

 aID    bID
   1      1
   1      2
   2      5
   2      3
   1      3
   3      5

I have this query: session.query(JT.aID).filter(JT.bID.in_(ids)).all()

This query returns the aID values 1, 2 and 3 because they all have rows with either 3 or 5 in the bID column. What I want the query to return is 2 because that is the only aID value that has all values of the ids list in its bID column.

Don't know how to explain the problem better, but how can I get to the result?

Solution 1

You are looking for a query that works on sets of rows. I think a group by with having clause is the best approach:

select aid
from jt
where bid in (<your list>)
group by aid
having count(distinct bid) = 2

If you can put the ids that you desire in a table, you can do the following more generic approach:

select aid
from jt join
     bids
     on jf.bid = bids.bid
group by aid
having count(distinct jt.bid) = (select count(*) from bids)

Solution 2

Based on @Gordon Linoff answer and with two tables A and B where A has a relation one- to-many towards B called A.bs the SqlAlchemy equivalent would be:

from sqlalchemy import func  
session.query(A).join(B).filter(B.id.in_(<your_list>)).group_by(A.id).having(func.count(A.bs) == len(<your_list>)).all()

Comments

[boadescriptor]

Does SqlAlchemy have a specific syntax for this or does it have to be in SQL?

[cdaddr]

SQLAlchemy is able to produce queries like these with its SQL expression language, pretty well covered in its docs though you might need to experiment a bit with the Python syntax before you get it. Alternatively, sqlalchemy also lets you issue direct queries as text literals. Lastly, if you have a real need to build the query dynamically, assembling it from component parts based on what's happening in your program logic, then you can go at it with sqlalchemy's ORM tools, but normally you wouldn't choose that longer and slower method unless it was really needed.