On 8/3/15 1:01 PM, Mike Bayer wrote:
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
OK, this is not as much of a bug as I thought, though I might be able to
do something.
The join here has no choice but to auto-alias the "A" target when it
joins on B.a_list. When you then join with a second call to .join(),
it assumes you want to again join from B. You need to be using
from_joinpoint here:
q = q.join(A.x_list, from_joinpoint=True)
it then knows you want to join from A and not B.
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
<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.