如何修正SQLAlchemy三表外连接查询以获取指定字段结果

错误点说明 #

你编写的第二种查询存在两个核心问题：

• 关联关系错误：Product表与User表没有直接外键关联，Product实际是通过product_id字段与OrderItem表关联，你之前写的Product.id == User.id关联条件完全不成立
• join调用参数错误：SQLAlchemy的outerjoin方法链式调用时，不需要重复传入已经在查询上下文中的左表，且关联顺序不符合你的业务逻辑

修正后的查询代码 #

如果你要实现和第一种查询完全一致的结果（无订单的用户不返回、无对应商品的订单不返回），代码如下：

query = session.query(User.last_name, OrderItem.qty, Product.name)\
    .select_from(User)\
    .outerjoin(OrderItem, OrderItem.user_id == User.id)\
    .join(Product, Product.id == OrderItem.product_id)

# 遍历输出结果
for last_name, qty, name in query.all():
    print(last_name, qty, name)

如果你需要保留所有用户（即使没有订单的用户也返回，对应qty和name为NULL），把第二个join也改成outerjoin即可：

query = session.query(User.last_name, OrderItem.qty, Product.name)\
    .select_from(User)\
    .outerjoin(OrderItem, OrderItem.user_id == User.id)\
    .outerjoin(Product, Product.id == OrderItem.product_id)

输出验证 #

第一种修正后的查询输出和你提供的第一种查询结果完全一致：

Cleese 9 Rabbit
Cleese 2 Rabbit
Chapman 2 Rabbit
Chapman 3 Parrot
Chapman 20 Parrot

内容的提问来源于stack exchange，提问作者Dorota-Toskania