SqlAlchemy: filter to match all, not any values ​​in a list?

Question

I want to query the jump table for the value of the aID column, which matches all the values ​​of the identifier list ids=[3,5] in the bID column.

This is my connection 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 aID 1 , 2 and 3 because they all have rows with 3 or 5 in the bID column. I want to return query 2 because this is the only aID value that has all the values ​​in the ids list in the bID column.

I don’t know how to better explain the problem, but how can I get the result?

Answer 1

You are looking for a query that works with row sets. I think a group with an is condition is the best way:

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

If you can put the identifiers you want in the table, you can do the following more general approach:

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

Answer 2

Based on @ Gordon Linoff's answer and two tables A and B , where A is one-to-many related to B , called A.bs equivalent of SqlAlchemy, 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() 

Answer 3

Try:

  session.query(JT.aID).filter(not_(JT.bID.in_(ids))).all()