在SQLAlchemy中通过双重多对多定义关系

Question

我有三个表：Item（物品）、Shelve（架子）和Cabinet（柜子）。

一个Cabinet可以有很多个Shelve，而一个Shelve又可以有很多个Item：

Item
    id: int
    shelve_id: int

Shelve
    id: int
    cabinet_id: int

Cabinet:
    id: id

我想要建立一个方便的关系，让Cabinet和一系列Item关联起来：

class Cabinet(DeclarativeBase):
    # ...
    items: Mapped[List["Item"]] = relationship()

但是，这样做会出现一个错误，提示找不到Cabinet和Item之间的关系。我能理解这个错误，因为它们之间的关系并不明显，而Shelve就像是一个连接表。

我该如何在SQLAlchemy中实现这个关系呢？

我在阅读关于关系的文档，但不太确定哪个适用。我需要一个二次连接吗？
我找到的其他问题涉及到双向多对多关系，这听起来不太一样：通过两个多对多表的SQLAlchemy关系

Answer 1

看起来只需要在任意一个关系中加上 secondary="shelves" 就可以了。下面是我想要的关系的完整模型：

class Cabinet(DeclarativeBase):
    __tablename__ = "cabinets"

    id: Mapped[int] = mapped_column(primary_key=True)

    shelves: Mapped[List["Shelve"]] = relationship(back_populates="cabinet")

    # Also provide indirect association:
    items: Mapped[List["Item"]] = relationship(
        secondary="shelves",
        back_populates="cabinet",
        viewonly=True,  # Prevent SAWArning
    )


class Shelve(DeclarativeBase):
    __tablename__ = "shelves"

    id: Mapped[int] = mapped_column(primary_key=True)
    cabinet_id: Mapped[int] = mapped_column(ForeignKey("cabinets.id"))

    items: Mapped[List["Item"]] = relationship(back_populates="shelve")
    cabinet: Mapped["Cabinet"] = relationship(back_populates="shelves")


class Item(DeclarativeBase):
    __tablename__ = "items"

    id: Mapped[int] = mapped_column(primary_key=True)
    shelve_id: Mapped[int] = mapped_column(ForeignKey("shelves.id"))

    shelve: Mapped["Shelve"] = relationship(back_populates="items")

    # Also provide indirect association:
    cabinet: Mapped["Item"] = relationship(
        secondary="shelves",
        back_populates="items",
        viewonly=True,  # Prevent SAWArning
    )

不过我还是有点困惑，因为如果不加 viewonly=True，我会收到来自SA的警告：

SA警告：关系 'Cabinet.items' 会把列 cabinet.id 复制到列 shelves.cabinet_id，这和以下关系冲突： 'Shelves.cabinet'（把 cabinets.id 复制到 shelves.cabinet_id）， 'Cabinets.shelves'（把 cabinets.id 复制到 shelves.cabinet_id）。如果这不是你的本意，考虑一下这些关系是否应该用 back_populates 连接，或者如果它们是只读的，是否应该对一个或多个应用 viewonly=True。对于外键约束部分重叠的少见情况，可以使用 orm.foreign() 注解来隔离应该写入的列。为了消除这个警告，可以在 'Cabinet.items' 关系中添加参数 'overlaps="shelves,cabinet"'。 （关于这个警告的背景可以查看： https://sqlalche.me/e/20/qzyx） （这个警告是来自 configure_mappers() 过程，它是在用户发起操作后自动调用的。）

我真的不太明白为什么会这样。