Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
451 views
in Technique[技术] by (71.8m points)

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

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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()

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...