如何在SQLAlchemy中实现Cabinet到Item的间接关联？

要实现Cabinet直接关联所有下属Item，需要利用SQLAlchemy关系的secondary和join参数，通过中间表Shelve建立跨表关联，具体代码如下：

完整模型定义 #

from sqlalchemy import Integer, ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
from typing import List

class Base(DeclarativeBase):
    pass

class Cabinet(Base):
    __tablename__ = "cabinet"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    
    # 先定义Cabinet到Shelve的基础一对多关系
    shelves: Mapped[List["Shelve"]] = relationship(back_populates="cabinet")
    
    # 定义直接获取所有Item的关联属性
    items: Mapped[List["Item"]] = relationship(
        "Item",
        secondary="shelve",  # 指定中间关联表为Shelve的数据库表名
        primaryjoin="Cabinet.id == Shelve.cabinet_id",
        secondaryjoin="Shelve.id == Item.shelve_id",
        viewonly=True  # 该关系仅用于查询，不支持写入（跨两级关联无法自动处理写入逻辑）
    )

class Shelve(Base):
    __tablename__ = "shelve"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    cabinet_id: Mapped[int] = mapped_column(Integer, ForeignKey("cabinet.id"))
    
    cabinet: Mapped["Cabinet"] = relationship(back_populates="shelves")
    items: Mapped[List["Item"]] = relationship(back_populates="shelve")

class Item(Base):
    __tablename__ = "item"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    shelve_id: Mapped[int] = mapped_column(Integer, ForeignKey("shelve.id"))
    
    shelve: Mapped["Shelve"] = relationship(back_populates="items")

参数说明 #

• secondary="shelve"：指定中间关联的数据库表名（即Shelve类对应的表）
• primaryjoin：定义Cabinet与中间表Shelve的关联条件
• secondaryjoin：定义中间表Shelve与目标表Item的关联条件
• viewonly=True：必须设置，因为这种跨两级的关联无法自动处理写入逻辑（比如直接给cabinet.items添加Item时，无法自动分配shelve_id），仅用于查询场景

替代方案（无需额外关系定义） #

如果不需要专门的属性，也可以通过已有的shelves关系间接查询：

# 获取某个cabinet的所有item
all_items = [item for shelf in cabinet.shelves for item in shelf.items]

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