Right, I can unpack the relationship between A and B into column expressions manually, but this will break for `secondaryjoin` relationships. I guess I can unpack those manually as well, by introducing the secondary table expression when needed. I will try that.
On 12 February 2018 at 11:24, Mike Bayer <mike...@zzzcomputing.com> wrote: > 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 a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit https://groups.google.com/d/ > topic/sqlalchemy/s3YKpfv3j1Y/unsubscribe. > To unsubscribe from this group and all its topics, 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.