Hi again,
Full code: https://gist.github.com/dpwrussell/8ecca88f642cca003999
I have an structure linked together like so. A-B is a Many-To-Many and uses
an association table. A and B are both subclasses of common base Object.
A
└── B
I also have an object X that can be linked to any type of object: A or B.
I can easily run a query that returns all objects that have a certain X
object linked to it.
I also need to be able to run a query which gets all the B objects where
the A parent has a certain X object linked to it.
Chained:
SELECT object.type AS object_type, b.id AS b_id, object.id AS object_id,
object.name AS object_name
FROM object JOIN b ON object.id = b.id JOIN a_b_association AS
a_b_association_1 ON b.id = a_b_association_1.b_id JOIN (object AS object_1
JOIN a AS a_1 ON object_1.id = a_1.id) ON a_1.id = a_b_association_1.a_id
JOIN x ON object.id = x.obj_id
WHERE x.name = %(name_1)s
2015-08-03 10:53:03,474 INFO sqlalchemy.engine.base.Engine {'name_1': 'x1'}
Multiple as-clause:
SELECT object.type AS object_type, b.id AS b_id, object.id AS object_id,
object.name AS object_name
FROM object JOIN b ON object.id = b.id JOIN a_b_association AS
a_b_association_1 ON b.id = a_b_association_1.b_id JOIN (object AS object_1
JOIN a AS a_1 ON object_1.id = a_1.id) ON a_1.id = a_b_association_1.a_id
JOIN x ON object_1.id = x.obj_id
WHERE x.name = %(name_1)s
2015-08-03 10:53:03,480 INFO sqlalchemy.engine.base.Engine {'name_1': 'x1'}
<Object(id='2', name='b1')>
The difference is subtle. In the multiple on-clause case the JOIN to the x
table is conducted using the alias (object_1) created during the previous
JOIN. This is the behaviour that I would expect and gives the correct
result. In the chained case, the original object reference is used, giving
incorrect results (none in this case).
The SQLAlchemy manual seems to suggest that these should be equivalent so
I'm wondering if there is a bug there?
If I'm reading the manual correctly, I can ordinarily use JOIN aliases to
explicitly avoid this kind of thing, but in this case, I am not specifying
this join myself, it is being built from the joined table inheritance.
I am going to use the on-clause technique for now to get around this, but
it would be good to know (especially if this is not a bug) if I should be
handling this differently in general?
Thanks a lot,
Douglas
--
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.
For more options, visit https://groups.google.com/d/optout.
