[Python] Can't sort by field on joined table

I have a model which I'm joining subsequently onto 3 other models:

statement = select(Item).filter(Item.id == item_id)
if include_purchases:
statement = statement.options(
joinedload(Item.purchases)
.joinedload(Purchase.receipt)
.joinedload(Receipt.store)
).order_by(Receipt.date.desc())
else:
statement = statement.limit(1)


However, it errors:

| sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.UndefinedTableError'>: invalid reference to FROM-clause entry for table "receipts"
| HINT: Perhaps you meant to reference the table alias "receipts_1".
| [SQL: SELECT items.id, items.name, items.notes, stores_1.id AS id_1, stores_1.name AS name_1, receipts_1.id AS id_2, receipts_1.store_id, receipts_1.date, receipts_1.notes AS notes_1, purchases_1.id AS id_3, purchases_1.item_id, purchases_1.receipt_id, purchases_1.price, purchases_1.amount, purchases_1.notes AS notes_2
| FROM items LEFT OUTER JOIN purchases AS purchases_1 ON items.id = purchases_1.item_id LEFT OUTER JOIN receipts AS receipts_1 ON receipts_1.id = purchases_1.receipt_id LEFT OUTER JOIN stores AS stores_1 ON stores_1.id = receipts_1.store_id
| WHERE items.id = $1::INTEGER ORDER BY receipts.date DESC]


It's creating aliases for the joined loads, so the order by doesn't work directly, but I'm missing in the docs how to actually resolve it.

Continue reading...