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