SQLAlchemy Order By Foreign Key Table Column

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.

http://docs.sqlalchemy.org/en/rel_0_7/orm/loading.html

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()
SELECT addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address, addresses_1.user_id AS addresses_1_user_id, users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users LEFT OUTER JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id WHERE users.name = ? ORDER BY addresses.email_address <– this part is wrong ! [‘jack’]

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()
SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password FROM users JOIN addresses ON users.id = addresses.user_id WHERE users.name = ? ORDER BY addresses.email_address [‘jack’]

Published by Yuji Tomita

I'm a human person. I excel in crafting holistic technology solutions that extend beyond a narrow technical scope.

Leave a Comment