CODEWITHSUNDEEP
sqlsqlalchemy
Gabriel
Gabriel

Reputation: 10904

Set intersection on results of sqlalchemy queries

I'm attempting to return the overlap of the results of two ORM queries that would both return a list of integers. I have two tables and I know how to perform the simple filtered queries on them. 

query1 = session.query(T1.UID).filter(T1.date < now)
query2 = session.query(T2.UID).filter(T2.date < now)

What I'd like is a query that will return the UIDs that appear in both of the results that would be returned by these queries. The individual queries would return a large amount of data but I'm looking for the relatively small overlap. Conceptually something like <result of query1>.in_(<result of query2>).

Upvotes: 0

Views: 2109

Answers (1)

van
van

Reputation: 77072

Simple inner join would do it:

q = (session.query(T1.UID)
     .join(T2, T1.UID == T2.UID)
     .filter(T1.date < now)
     .filter(T2.date < now)
     )

If you really want to use your two queries, below would be the way:

query1 = query1.filter(T1.UID.in_(query2.subquery()))

Also make sure you have an index on both columns.

Upvotes: 1

Related Questions