使用SQLAlchemy关联查询时无法按关联表字段排序的问题

方法一：通过关系路径属性排序 #

直接使用模型关联链的属性指定排序字段，SQLAlchemy会自动解析到joinedload生成的表别名，无需手动处理别名：

from sqlalchemy import select, desc

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(desc(Item.purchases.receipt.date))  # 用关联链属性替代直接引用Receipt.date
else:
    statement = statement.limit(1)

方法二：改用contains_eager显式关联 #

如果需要更灵活控制关联逻辑，可改用contains_eager配合显式JOIN，直接使用模型类字段排序：

from sqlalchemy import select, desc

statement = select(Item).filter(Item.id == item_id)
if include_purchases:
    # 显式LEFT JOIN关联表，isouter=True保持和joinedload默认左连接行为一致
    statement = statement.join(Item.purchases, isouter=True)\
                        .join(Purchase.receipt, isouter=True)\
                        .join(Receipt.store, isouter=True)
    # 使用contains_eager加载关联数据
    statement = statement.options(
        contains_eager(Item.purchases)
        .contains_eager(Purchase.receipt)
        .contains_eager(Receipt.store)
    ).order_by(Receipt.date.desc())
else:
    statement = statement.limit(1)

方法三：显式指定表别名 #

通过aliased创建表别名，在joinedload中绑定别名，排序时直接使用别名字段：

from sqlalchemy import select, desc, aliased

# 创建各模型的别名
purchase_alias = aliased(Purchase)
receipt_alias = aliased(Receipt)
store_alias = aliased(Store)

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

内容的提问来源于stack exchange，提问作者Rohit