To order by a foreign key value, make sure you do a proper `.join` of the table you want to reference in addition to `.options` and `joinedload` or `joinedload_all`, which is purely to retrieve data from each related row.
SQLAlchemy never ceases to amaze.. I’m glad I made the switch, as it is making me a better coder by not shielding me from the intricacies of SQL.
How joinedload() in particular achieves this result of not impacting entity rows returned in any way is that it creates an anonymous alias of the joins it adds to your query, so that they can’t be referenced by other parts of the query. For example, the query below uses joinedload() to create a LEFT OUTER JOIN from users to addresses, however the ORDER BY added against Address.email_address is not valid – the Address entity is not named in the query:
>>> jack = session.query(User).\ ... options(joinedload(User.addresses)).\ ... filter(User.name=='jack').\ ... order_by(Address.email_address).all()
Above, ORDER BY addresses.email_address is not valid since addresses is not in the FROM list. The correct way to load the User records and order by email address is to use Query.join():
>>> jack = session.query(User).\ ... join(User.addresses).\ ... filter(User.name=='jack').\ ... order_by(Address.email_address).all()