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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
