the basic idea (assuming ORM style) would be like:
e = aliased(Engagement)
ee = aliased(Engagement)
e1 = exists().where(and_(
ee.id == 156,
ee.target_id == e.target_id,
ee.candidate_id == e.candidate_id
))
e2 = exists().where(and_(
Message.id == e.id,
e1
))
q = session.query(Message).filter(e2)
if that needs help with the correlation then we'd add some correlate()
calls, but it shouldn't, since the FROM nesting here is clear.
On 07/08/2016 03:21 AM, Tim Chen wrote:
Hi, I'm wondering what the correct syntax is for a nested correlated
exists subquery?
I have Message and Engagement declarative tables and here is the query
i'm trying to replicate:
|
select *
from message m
where exists (
select 1 from engagement e
where m.engagement_id = e.id
and exists (
select 1 from engagement ee
where ee.id = 156
and ee.target_id = e.target_id
and ee.candidate_id = e.candidate_id
)
);
|
Much appreciated!
-tim
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.