[sqlalchemy] [Q] Struggle with exists
Hello. I don't know how to write the following query in SA (using constructs such as query, select, exists): 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 ) Can you help me? (I have read tutorial and API documentation several times but I still don't get it.) Thank you in advance, 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.
Re: [sqlalchemy] [Q] Struggle with exists
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 lenart...@volny.cz 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 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.
Re: [sqlalchemy] [Q] Struggle with exists
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 lenart...@volny.cz 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 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.
