On Sat, Feb 10, 2018 at 6:17 PM, Christophe Biocca <christophe.bio...@gmail.com> wrote: > 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.
there's no joins in the statement you describe, you can get that close-to-identical SQL using union() and exists() normally, full example: from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.declarative import declared_attr Base = declarative_base() class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) bs = relationship("B") class B(Base): __tablename__ = 'b' id = Column(Integer, primary_key=True) a_id = Column(ForeignKey('a.id')) session = Session() """ 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 ) ) """ q = session.query(A).filter( session.query(B.id).filter(B.id > 5).filter(B.a_id == A.id).correlate(A). union( session.query(B.id).filter(B.id < 10).filter(B.a_id == A.id). correlate(A) ).exists() ) print(q) """ SELECT a.id AS a_id FROM a WHERE EXISTS ( SELECT 1 FROM ( SELECT b.id AS b_id FROM b WHERE b.id > :id_1 AND b.a_id = a.id UNION SELECT b.id AS b_id FROM b WHERE b.id < :id_2 AND b.a_id = a.id ) AS anon_1 ) """ > > -- > 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. -- 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.