clarify what should happen with query(A).select_from(A).join(B.a)

[Former user]

(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.

[Former user]

(original author: rbu) duplicate report on our side, see also #2724

[Mike Bayer]

yeah that's amazing how issue reports come in pairs like that right? this behavior has been the same for years.

[Mike Bayer]

it really falls under the realm of undefined behavior at the moment.

[Mike Bayer]

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.