all of those syntaxes are supposed to be exactly equivalent so this is a
major issue:
https://bitbucket.org/zzzeek/sqlalchemy/issues/3505/join-targeting-broken-for-joined-inh
On 8/3/15 11:13 AM, Douglas Russell wrote:
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 objectJOIN 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 (objectAS
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-0310:53:03,474INFO 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 objectJOIN 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 (objectAS
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-0310:53:03,480INFO 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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.