SQLAlchemy：文件夹与Item关联排序及级联删除失效问题

Folder包含Item，每个Item仅能归属一个Folder，需记录Item在Folder中的顺序。原实现采用数组存储Item ID排序，但频繁出现排序列表与实际Item集合不一致的情况。改用关联对象模式（将多对多转为一对多）后，删除Folder时关联表记录被删除，但Item遗留为孤儿数据，级联删除失效。

原实现代码 #

class Folder(Base):
   __tablename__ = "folder"
   id = Column(Integer, primary_key=True)

   items = relationship(
      "Item",
      back_populates="folder",
      cascade="all, delete-orphan",
   )

   item_ordering = Column(ARRAY(String), default=[])

class Item(Base):
   __tablename__ = "item"
   id = Column(Integer, primary_key=True)
   folder_id = Column(String, ForeignKey("folder.id", ondelete="CASCADE"))
   folder = relationship("Folder", back_populates="signal_boards")

改进后代码 #

class Folder(Base):
    __tablename__ = "folder"
    id = Column(Integer, primary_key=True)

    items = relationship(
        "Item",
        secondary="folder_item_association",
        back_populates="folder",
        order_by="desc(folder_item_association.c.order)",
        single_parent=True,  # N:M -> 1:M
        cascade="all, delete-orphan",
    )
    item_associations = relationship(
        "FolderItemAssociation",
        back_populates="folder",
        order_by="desc(FolderItemAssociation.order)",
        single_parent=True,  # N:M -> 1:M
        cascade="all, delete-orphan",
    )


class FolderItemAssociation(Base):
    __tablename__ = "folder_item_association"

    project_id = Column(
        Integer,
        ForeignKey("folder.id", ondelete="CASCADE"),
        primary_key=True,
    )
    item_id = Column(
        Integer,
        ForeignKey("item.id", ondelete="CASCADE"),
        primary_key=True,
        unique=True,  # one workspace can be associated with only one project
    )
    order = Column(
        BigInteger,
        autoincrement=True,
    )

    folder = relationship(
        "Folder",
        back_populates="item_associations",
    )
    item = relationship(
        "Item",
        back_populates="folder_association",
    )

class Item(Base):
    __tablename__ = "item"
    id = Column(Integer, primary_key=True)

    folder = relationship( 
        "Folder",
        secondary="folder_item_association",
        back_populates="items",
        passive_deletes=True,
        uselist=False,
    )
    folder_association = relationship(
        "FolderItemAssociation",
        back_populates="item",
        passive_deletes=True,
        uselist=False,
    )

测试用例 #

def test_item_objects_should_be_deleted():

    with session_scope() as session:
        folder = Folder(
            id="test_fid",
            items=[
                Item(
                    id="test_iid",
                )
            ]
        )
        session.add(folder)
        session.commit()

        counts = (
            session.query(Folder).count(),
            session.query(FolderItemAssociation).count(),
            session.query(Item).count(),
        )  # -> (1, 1, 1)

        session.query(Folder).delete()
        session.commit()

        counts = (
            session.query(Folder).count(),
            session.query(FolderItemAssociation).count(),
            session.query(Item).count(),
        )  # -> (0, 0, 1)


        assert counts == (0,) * len(counts), f"{counts} is not just zeros."

核心问题 #

1. 关联表字段错误：FolderItemAssociation中project_id应为folder_id，外键关联对象不匹配。
2. 级联逻辑断裂：批量删除（session.query(Folder).delete()）仅触发数据库层面的外键级联，但Item与Folder无直接外键，无法触发Item删除；同时关联对象与Item的关系未配置级联规则。
3. 关系配置冗余：Folder与Item的secondary关系搭配single_parent=True不符合一对多关联对象的正确配置方式。

修正后的代码 #

模型代码

class Folder(Base):
    __tablename__ = "folder"
    id = Column(Integer, primary_key=True)

    # 关联对象关系，删除Folder时自动删除关联记录
    item_associations = relationship(
        "FolderItemAssociation",
        back_populates="folder",
        order_by="desc(FolderItemAssociation.order)",
        cascade="all, delete-orphan",
    )

    # 便捷属性：直接获取排序后的Item列表
    @property
    def items(self):
        return [assoc.item for assoc in self.item_associations]


class FolderItemAssociation(Base):
    __tablename__ = "folder_item_association"

    folder_id = Column(
        Integer,
        ForeignKey("folder.id", ondelete="CASCADE"),
        primary_key=True,
    )
    item_id = Column(
        Integer,
        ForeignKey("item.id"),
        primary_key=True,
        unique=True,  # 确保一个Item仅归属一个Folder
    )
    order = Column(
        BigInteger,
        autoincrement=True,
    )

    folder = relationship(
        "Folder",
        back_populates="item_associations",
    )
    # 配置级联：删除关联记录时自动删除对应的Item
    item = relationship(
        "Item",
        back_populates="folder_association",
        cascade="all, delete-orphan",
        uselist=False,
    )


class Item(Base):
    __tablename__ = "item"
    id = Column(Integer, primary_key=True)

    folder_association = relationship(
        "FolderItemAssociation",
        back_populates="item",
        uselist=False,
    )

    # 便捷属性：直接获取所属Folder
    @property
    def folder(self):
        return self.folder_association.folder if self.folder_association else None

修正后的测试用例

def test_item_objects_should_be_deleted():
    with session_scope() as session:
        folder = Folder(id=1)
        item = Item(id=1)
        assoc = FolderItemAssociation(folder=folder, item=item)
        session.add(folder)
        session.commit()

        counts = (
            session.query(Folder).count(),
            session.query(FolderItemAssociation).count(),
            session.query(Item).count(),
        )  # -> (1, 1, 1)

        # 使用对象删除触发ORM级联逻辑，而非批量删除
        target_folder = session.query(Folder).first()
        session.delete(target_folder)
        session.commit()

        counts = (
            session.query(Folder).count(),
            session.query(FolderItemAssociation).count(),
            session.query(Item).count(),
        )  # -> (0, 0, 0)

        assert counts == (0,) * len(counts), f"{counts} is not just zeros."

关键修改说明 #

1. 修正关联表字段：将project_id改为folder_id，确保外键关联到正确的表。
2. 配置级联规则：在FolderItemAssociation.item关系中添加cascade="all, delete-orphan"，删除关联记录时自动删除Item；Folder.item_associations的级联规则确保删除Folder时自动删除所有关联记录。
3. 改用对象级删除：批量删除仅生成数据库层面的DELETE语句，不会触发ORM的对象级联逻辑；通过session.delete(folder)触发完整的级联链。
4. 便捷属性替代冗余关系：用@property实现items和folder的便捷访问，避免secondary关系带来的配置冲突。

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