clarify what should happen with query(A).select_from(A).join(B.a)
(original reporter: rbu) We had quite a hard time figuring out the JOIN order as generated by a query(A).join(B) statement. All examples in the documentation have A as left and B as right side. Apparently though, when giving an explicit criterion as query(A).join(B.a), B will be the left side even when adding a select_from.
Details:
# surprising
>>> print DBSession.query(User.id).outerjoin(Track.user)
SELECT user.id AS user_id
FROM track LEFT OUTER JOIN user ON user.id = track.user_id
# bug?
>>> print DBSession.query(User.id).select_from(User).outerjoin(Track.user)
SELECT user.iduser AS user_id
FROM track LEFT OUTER JOIN user ON user.id = track.user_id
# expected
>>> print DBSession.query(User.id).outerjoin(User.tracks)
SELECT user.iduser AS user_id
FROM user LEFT OUTER JOIN track ON user.id = track.user_id
This magic ordering based on the explicit criterion given should be covered in the documentation, but we did not find such note.
Comments (6)
-
Account Deleted -
repo owner yeah that's amazing how issue reports come in pairs like that right? this behavior has been the same for years.
-
repo owner it really falls under the realm of undefined behavior at the moment.
-
repo owner - changed component to documentation
- changed title to clarify what should happen with query(A).select_from(A).join(B.a)
- changed milestone to 0.8.xx
-
repo owner yeah I've looked into seeing how an expression like:
session.query(A).select_from(A).join(B.a)
could establish the join as "A to B", but this would be an awkward exception case added to what is otherwise becoming a fairly consistent system. Using the relationship for the join, "B.a", means "join from B to A", and that takes precedence here. This works well because "B.a" contains both the two endpoints, the ON clause, and the direction, i.e. which side is left and which is right. If Query were to second-guess this, and look at "select from", that becomes more complicated, in the case where we are already "selecting from" many things, such as
query(A).select_from(A).join(A.cs).join(C.bs)
etc. - it means we have to look at each of "A", "C", and "B" and make a guess which is the best "left side" to use.I do sometimes have the case where I don't have a backref set up and would like to use the reverse side, so I'm wondering if a new symbol like
session.query(A).join(B.a.reversed)
might be possible. -
repo owner - changed milestone to blue sky
- Log in to comment
(original author: rbu) duplicate report on our side, see also
#2724