SQLAlchemy中的物化路径模式实现树形结构存储-优快云博客

本文链接：https://blog.youkuaiyun.com/gitblog_00254/article/details/148415599

SQLAlchemy中的物化路径模式实现树形结构存储

sqlalchemy The Database Toolkit for Python 项目地址: https://gitcode.com/gh_mirrors/sq/sqlalchemy

什么是物化路径模式

物化路径(Materialized Path)是一种在关系型数据库中存储和查询树形结构数据的方法。它通过在每条记录中存储从根节点到当前节点的完整路径信息，实现了高效的祖先和后代查询。

这种模式在查询性能和维护成本之间取得了很好的平衡，相比邻接表(Adjacency List)模型有更好的查询性能，相比嵌套集(Nested Sets)模型有更简单的维护逻辑。

实现原理

物化路径的核心思想是为每个节点存储一个表示其在树中位置的路径字符串，类似于文件系统中的路径表示：

1
1.2
1.3
1.3.4
1.3.5
1.3.6
1.7
1.7.8
1.7.9
1.7.9.10
1.7.11

路径字符串通常使用点号(.)作为分隔符，包含从根节点到当前节点的所有祖先节点的ID。

SQLAlchemy实现详解

基础模型定义

首先定义一个基本的Node模型，包含id和path两个字段：

class Node(Base):
    __tablename__ = "node"
    
    id = Column(Integer, primary_key=True, autoincrement=False)
    path = Column(String(500), nullable=False, index=True)

后代关系查询

通过SQLAlchemy的关系特性，我们可以方便地定义后代查询：

descendants = relationship(
    "Node",
    viewonly=True,
    order_by=path,
    primaryjoin=remote(foreign(path)).like(path.concat(".%")),
)

这里使用了LIKE操作符，查找所有path以当前节点path开头的节点，即所有后代节点。

祖先关系查询

祖先查询稍微复杂一些，需要使用SQL函数处理路径字符串：

secondary = select(
    id.label("id"),
    func.unnest(
        cast(
            func.string_to_array(
                func.regexp_replace(path, r"\.?\d+$", ""), "."
            ),
            ARRAY(Integer),
        )
    ).label("ancestor_id"),
).alias()

ancestors = relationship(
    "Node",
    viewonly=True,
    secondary=secondary,
    primaryjoin=id == secondary.c.id,
    secondaryjoin=secondary.c.ancestor_id == id,
    order_by=path,
)

这段代码通过正则表达式去掉路径末尾的当前节点ID，然后将剩余部分转换为数组，再展开为多行记录，最后通过这些ID关联到祖先节点。