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 <[email protected]> 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 [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.
