1. Michael Bayer
  2. sqlalchemy
  3. Issues


Issue #2722 new

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

Anonymous created an issue

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


# 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)

  1. Michael Bayer repo owner

    yeah I've looked into seeing how an expression like:


    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.

  2. Log in to comment