Flask-SQLAlchemy递归查询层级数据如何按父子嵌套结构排序？

要实现你需要的父子嵌套顺序（深度优先排序），只需要在递归CTE中新增一个层级路径字段，最终查询时按该字段排序即可。

修改步骤 #

1. 确认导入SQLAlchemy的func工具用于字符串拼接：

from sqlalchemy import func

2. 调整递归CTE定义，新增路径字段并按路径排序，修改后完整代码如下：

# post_id = 数据库中帖子的唯一ID
with db_session_manager() as db_session:
    # 构造CTE使用的过滤条件
    filters = [
        Post.post_id == post_id,
        Post.parent_id == None
    ]
    # 构造自引用层级查询，新增path字段存储层级路径
    posts_hierarchy = (
        db_session.query(
            Post, 
            literal(0).label('level'),
            # 根节点初始路径格式：/根节点post_id/
            func.concat('/', Post.post_id, '/').label('path')
        )
        .filter(*filters)
        .cte(name='post_hierarchy', recursive=True)
    )
    parent = aliased(posts_hierarchy, name="p")
    children = aliased(Post, name="c")
    posts_hierarchy = (
        posts_hierarchy.union_all(
            db_session.query(
                Post, 
                (parent.c.level + 1).label("level"),
                # 子节点路径 = 父节点路径 + 当前子节点post_id + /
                func.concat(parent.c.path, children.post_id, '/').label('path')
            )
            .filter(children.parent_id == parent.c.post_id)
            .filter(Post.post_id == children.post_id)
        )
    )         
    posts = (
        db_session.query(Post, posts_hierarchy.c.level)
        .select_entity_from(posts_hierarchy)
        # 按层级路径排序，自然实现深度优先的嵌套顺序
        .order_by(posts_hierarchy.c.path)
        .all()
    )

原理说明 #

• 路径字段会完整记录每个节点从根节点到当前节点的层级关系，比如根节点路径为/root_001/，第一个子节点路径为/root_001/child_001/，该子节点的所有后代路径都会以/root_001/child_001/为前缀
• 按路径字符串排序时，同一父节点的所有后代都会排列在该父节点之后、下一个同级节点之前，刚好匹配你需要的嵌套顺序
• 该方案兼容MySQL、PostgreSQL、SQLite等主流数据库，不需要调整数据库特定语法

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