FastAPI+SQLAlchemy多表joinedload报错及字段精简问题

1. 修复joinedload嵌套关联的错误 #

你遇到的AttributeError是因为Item.purchases是集合类型的关系（比如一对多，一个Item对应多条Purchase记录），它本身是集合属性而非单个Purchase实例，所以不能直接通过Item.purchases.receipt链式访问关联。正确的嵌套预加载方式是通过链式调用joinedload：

假设模型定义大致如下：

class Item(Base):
    __tablename__ = "items"
    id = Column(Integer, primary_key=True)
    purchases = relationship("Purchase", back_populates="item")

class Purchase(Base):
    __tablename__ = "purchases"
    id = Column(Integer, primary_key=True)
    item_id = Column(Integer, ForeignKey("items.id"))
    receipt_id = Column(Integer, ForeignKey("receipts.id"))
    item = relationship("Item", back_populates="purchases")
    receipt = relationship("Receipt", back_populates="purchases")

class Receipt(Base):
    __tablename__ = "receipts"
    id = Column(Integer, primary_key=True)
    store_id = Column(Integer, ForeignKey("stores.id"))
    purchases = relationship("Purchase", back_populates="receipt")
    store = relationship("Store", back_populates="receipts")

class Store(Base):
    __tablename__ = "stores"
    id = Column(Integer, primary_key=True)
    receipts = relationship("Receipt", back_populates="store")

正确的预加载写法：

from sqlalchemy.orm import joinedload

statement = statement.options(
    # 先预加载Item的purchases集合
    joinedload(Item.purchases)
        # 再预加载每个Purchase对应的receipt
        .joinedload(Purchase.receipt)
        # 最后预加载每个Receipt对应的store
        .joinedload(Receipt.store)
)

如果是优化集合关联的懒加载（避免N+1查询），也可以用selectinload，写法类似：

from sqlalchemy.orm import selectinload

statement = statement.options(
    selectinload(Item.purchases)
        .selectinload(Purchase.receipt)
        .selectinload(Receipt.store)
)

2. 精简查询字段数量 #

方式1：针对模型指定加载字段（保留模型实例） #

结合load_only，可以为每个模型指定只加载需要的字段，同时配合预加载关联：

from sqlalchemy.orm import joinedload, load_only

statement = statement.options(
    joinedload(Item.purchases)
        .load_only(Purchase.id, Purchase.amount)  # 只加载Purchase的指定字段
        .joinedload(Purchase.receipt)
        .load_only(Receipt.id, Receipt.date)  # 只加载Receipt的指定字段
        .joinedload(Receipt.store)
        .load_only(Store.id, Store.name)  # 只加载Store的指定字段
).options(load_only(Item.id, Item.name))  # 只加载Item的指定字段

方式2：直接指定查询字段（返回字段元组，无完整模型） #

如果不需要完整的模型实例，仅需展示数据，可以用with_entities直接选择所需字段，包括关联表的字段：

from sqlalchemy import select

statement = select(
    Item.id,
    Item.name,
    Purchase.amount,
    Receipt.date,
    Store.name.label("store_name")
).join(Item.purchases).join(Purchase.receipt).join(Receipt.store)

这种方式会返回字段元组或命名元组，能最大程度精简查询的字段数量。

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