I'm having some trouble using the has() operator to avoid a long chain
of joins in a select.
A stripped-down version of my schema looks like this: a Caller has a
(phone) number. A Caller can create Connections (each to a phone
number). A Connection can involve one or more Tasks, each of which
involves zero or more Actions. Each one-to-many is represented by a
bog-standard mapper on an ORM object:
callers = Table('callers', metadata,
Column('id', Integer, primary_key=True),
Column('number', String(20)))
connections = Table('connections', metadata,
Column('id', Integer, primary_key=True),
Column('phone_number', String(10)),
Column('caller_id', Integer, ForeignKey('callers.id')))
tasks = Table('tasks', metadata,
Column('id', Integer, primary_key=True),
Column('connection_id', Integer, ForeignKey('connections.id')))
actions = Table('actions', metadata,
Column('id', Integer, primary_key=True),
Column('task_id', Integer, ForeignKey('tasks.id')),
Column('description', String(100)))
class Caller(object):
pass
class Connection(object):
pass
class Task(object):
pass
class Action(object):
pass
mapper(Action, actions)
mapper(Task, tasks, properties={
'actions':relation(Action, backref='task')})
mapper(Connection, connections, properties={
'tasks':relation(Task, backref='connection')})
mapper(Caller, callers, properties={
'connections':relation(Connection, backref='caller')})
So, to find out what actions have been initiated by a given caller,
I'm doing:
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.
Am I misusing has() here? The obvious workaround is explicitly
joining or explicitly sub-selecting, both of which appear to work, but
I was hoping that using has() would produce cleaner code. :-)
A runnable, commented version of the example is at
http://pastebin.com/m674fc403.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
