Hello. I have found a solution to my problem: correlate(). The following code produces SQL I want (see below):
q0 = session.query(Deal).join(ImportedPartnerShare).filter( Deal.main_deal_ext_id == cls.main_deal_ext_id, ImportedPartnerShare.partner_id == partner.id, ) q = session.query(cls).filter( exists(q0.with_entities('1').statement.correlate(cls.__table__)) ) q = q.options( joinedload(cls.advice_type, innerjoin=True), joinedload_all(cls.responsible_partner, Partner.personal_data), ) Ladislav Lenart On 18.4.2013 18:54, Ladislav Lenart wrote: > Hello. > > The following SA code: > > # cls = Advice > q0 = session.query(Deal).join(ImportedPartnerShare).filter( > Deal.main_deal_ext_id == cls.main_deal_ext_id, > ImportedPartnerShare.partner_id == partner.id, > ) > q = session.query(cls).filter(exists(q0.with_entities('1').statement)) > q = q.options( > joinedload(cls.advice_type, innerjoin=True), > joinedload_all(cls.responsible_partner, Partner.personal_data), > ) > > > produces this SQL: > > > SELECT * > FROM > advice > JOIN advice_type AS advice_type_1 ON > advice.advice_type_id = advice_type_1.id > LEFT OUTER JOIN partner AS partner_1 ON > advice.responsible_partner_id = partner_1.id > LEFT OUTER JOIN personal_data AS personal_data_1 ON > partner_1.personal_data_id = personal_data_1.id > WHERE > EXISTS ( > SELECT 1 > FROM > advice, -- <-- #### How can I get rid of this? #### > deal > JOIN imported_partner_share ON deal.id = > imported_partner_share.deal_id > WHERE > deal.main_deal_ext_id = advice.main_deal_ext_id > AND imported_partner_share.partner_id = %(partner_id_1)s > ) > > > but I want to reference the advice table from the top query in EXISTS like > this: > > > SELECT * > FROM > advice > JOIN advice_type ON advice.advice_type_id = advice_type.id > LEFT OUTER JOIN partner ON advice.responsible_partner_id = partner.id > LEFT OUTER JOIN personal_data AS personal_data ON > partner.personal_data_id = personal_data.id > WHERE > EXISTS ( > SELECT 1 > FROM > deal > JOIN imported_partner_share ON > deal.id = imported_partner_share.deal_id > WHERE > deal.main_deal_ext_id = advice.main_deal_ext_id > AND imported_partner_share.partner_id = %(partner_id_1)s > ) > > > How can I do that? > > > Thank you, > > Ladislav Lenart > > -- 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.