[sqlalchemy] [Q][SA 07.9] - Trouble with EXISTS that references main SELECT

[Ladislav Lenart]

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.