Thank you! I was missing the following bit(s):
q = session.query( exists(q1.with_entities('1').statement) | exists(q2.with_entities('1').statement) ) I knew about Query.statement but I did not figure out how to combine that with OR. It did not occur to me that I can write session.query(or_(...)) directly. with_entities() construct is also new to me, though I presume that SQL engines optimize SELECTs in EXISTS automatically. I must admit that I did not understand your example the first time I saw it. But once I run it in the debugger, everything has become clear and logical: session.query( # renders top-level SELECT or_( # q.exists() is a core construct and thus cannot accept # a query object. q.statement returns select represented # by the query, which IS a core construct. # q.with_entities('1') replaces q's SELECT... part. exists(q1.with_entities('1').statement), exists(q2.with_entities('1').statement), ) ) One unrelated question: What is the difference between Query.add_column() and Query.add_entity()? Thank you again, Ladislav Lenart On 1.3.2013 18:01, Michael Bayer wrote: > we should probably add a method to Query called exists() that just turns any > query into "EXISTS (SELECT 1)", here's how to make it work for now > > from sqlalchemy import exists > > q1 = session.query(ImportedClientShare) > q1 = q1.join(ImportedPartnerShare, > ImportedClientShare.deal_id == > ImportedPartnerShare.deal_id) > q1 = q1.join(Deal, ImportedClientShare.deal_id == Deal.id) > q1 = q1.filter( > ImportedClientShare.client_id == client_id, > ImportedPartnerShare.partner_id == partner_id, > Deal.external_id != None, > ) > > q2 = session.query(ClientShare) > q2 = q2.join(PartnerShare, ClientShare.deal_id == PartnerShare.deal_id) > q2 = q2.join(Deal, ClientShare.deal_id == Deal.id) > > q2 = q2.filter( > ClientShare.client_id == client_id, > PartnerShare.partner_id == partner_id, > Deal.external_id == None, > ) > > q = session.query(exists(q1.with_entities('1').statement) | > exists(q2.with_entities('1').statement)) > > > > On Mar 1, 2013, at 7:41 AM, Ladislav Lenart <lenart...@volny.cz> wrote: > >> SELECT EXISTS( >> SELECT 1 >> FROM >> imported_client_share >> JOIN imported_partner_share ON imported_client_share.deal_id = >> imported_partner_share.deal_id >> JOIN deal ON imported_client_share.deal_id = deal.id >> WHERE >> imported_client_share.client_id = :client_id >> AND imported_partner_share.partner_id = :partner_id >> AND deal.external_id IS NULL >> ) OR EXISTS( >> SELECT 1 >> FROM >> client_share >> JOIN partner_share ON client_share.deal_id = partner_share.deal_id >> JOIN deal ON client_share.deal_id = deal.id >> WHERE >> client_share.client_id = :client_id >> AND partner_share.partner_id = :partner_id >> AND deal.external_id IS NULL >> ) -- 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 http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.