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 [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.
