Thanks. It makes sense to me that you would alias in the JOIN as well as in the WHERE as you say in the issue. I'm tracking that and making use of the from_joinpoint
for now. On Monday, 3 August 2015 13:12:16 UTC-4, Michael Bayer wrote: > > > > 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 > > <https://www.google.com/url?q=https%3A%2F%2Fbitbucket.org%2Fzzzeek%2Fsqlalchemy%2Fissues%2F3505%2Fjoin-targeting-broken-for-joined-inh&sa=D&sntz=1&usg=AFQjCNHNU0RMx9j2gGpxoTIUzcaXauAdqA> > > > 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 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 [email protected] <javascript:>. > To post to this group, send email to [email protected] > <javascript:>. > 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 [email protected] <javascript:>. > To post to this group, send email to [email protected] > <javascript:>. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
