Let's say I have 2 models, A and B, with relationships A.bs (haslist=True) and B.a (haslist=False)
Doing a semi join is trivial: session.query(A).filter(A.bs.any(SomeConditionOnB)) session.query(B).filter(B.a.has(SomeConditionOnA)) Doing a regular join is also trivial: session.query(A).join(A.bs).filter(SomeConditionOnB) session.query(B).filter(B.a).filter(SomeConditionOnA) I'd like to generate the following statement: SELECT * FROM a WHERE (EXISTS ( SELECT 1 FROM b WHERE SomeConditionOnB AND b.a_id = a.id UNION SELECT 1 FROM b WHERE SomeOtherConditionOnB AND b.a_id = a.id ) ) Because in my case `WHERE EXISTS( foo UNION bar )` is much, much faster than `WHERE EXISTS( foo ) OR EXISTS( bar )`. I've figured out how do do it as a regular join (with `aliased(B, b1.union(b2))`) but that won't work (it'll return some rows repeatedly due to JOIN's behaviour vs the semijoin of EXISTS). `has` and `any` both take a criterion object (?) which means giving it an aliased object won't work either. I've tried directly using `A.b.expression` and ClauseAdapter based on my perusing of the source code, but it won't work with tables with secondary joins and the like. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.