[sqlalchemy] Re: has() and multiple tables
On 4/7/08, Jonathan Ellis [EMAIL PROTECTED] wrote: I'm confused -- this sample does correlate actions with tasks, but not tasks with connections. Intuitively it seems that X.has(Y) should always add a clause to the the exists for y.y_id = x.y_id. No? Mike cleared this up for me in IRC: X.prop.has(criterion) just means, exists(select 1 from child where child.id=parent.x_id AND criterion) so the more elegant way to do this is session.query(Action).filter(Action.task.has(Task.connection.has(Connection.caller==caller1))) -Jonathan --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: has() and multiple tables
On Tue, Apr 8, 2008 at 1:00 PM, Jonathan Ellis [EMAIL PROTECTED] wrote: session.query(Action).filter(Action.task.has(Task.connection.has(Connection.caller==caller1))) Yep--that's what I'm doing now. It has the effect of creating another nested subselect, but I'm not too concerned about that as both are relatively cheap EXISTS queries. -- Tim Lesher [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: has() and multiple tables
On 4/8/08, Tim Lesher [EMAIL PROTECTED] wrote: Yep--that's what I'm doing now. It has the effect of creating another nested subselect, but I'm not too concerned about that as both are relatively cheap EXISTS queries. I wouldn't be surprised if PG's optimizer can inline it anyway. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: has() and multiple tables
On Apr 7, 2008, at 4:54 PM, Tim Lesher wrote:
I'm having some trouble using the has() operator to avoid a long chain
of joins in a select.
session
.query(Action).filter(Action.task.has(Connection.caller==caller1))
However, this appears to generate a cartesian join on the 'tasks' and
'connections' in the EXISTS() subselect, which leads to extra actions
being returned.
for this kind of thing, you're joining across three tables, so you can
put an extra join condition in the has():
filter(Action.task.has(and_(Connection.caller==caller1,
Task.connection_id==Connection.id)))
or do it with join():
query(Action).join('task').filter(Task.has(Connection.caller==caller1))
I have an intuitive sense that there should be some nice syntax to get
has() and any() to join across more tables but its not occuring to me
at the moment.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: has() and multiple tables
On 4/7/08, Michael Bayer [EMAIL PROTECTED] wrote:
On Apr 7, 2008, at 4:54 PM, Tim Lesher wrote:
session
.query(Action).filter(Action.task.has(Connection.caller==caller1))
for this kind of thing, you're joining across three tables, so you can
put an extra join condition in the has():
filter(Action.task.has(and_(Connection.caller==caller1,
Task.connection_id==Connection.id)))
or do it with join():
query(Action).join('task').filter(Task.has(Connection.caller==caller1))
I have an intuitive sense that there should be some nice syntax to get
has() and any() to join across more tables but its not occuring to me
at the moment.
I'm confused -- this sample does correlate actions with tasks, but not
tasks with connections. Intuitively it seems that X.has(Y) should
always add a clause to the the exists for y.y_id = x.y_id. No?
-Jonathan
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
