SQL层级数据排序问题（子→父关系）：实现类文件系统结构排序

表结构说明 #

• Learning Path：类文件夹结构，可包含ExerciseSet或子LearningPath
• ExerciseSet：类文件结构
• LearningPathItem：关联表，用于将ExerciseSet/LearningPath绑定到父LearningPath，itemId关联目标对象ID，learningPath指向父节点ID

期望的嵌套结构示例 #

Learning Path 1
-> Exercise Set 1
-> Learning Path 2
-> -> Exercise Set 2
-> -> Exercise Set 3
-> -> Learning Path 3
-> -> -> Exercise Set 4
-> -> -> Exercise Set 5
-> Exercise Set 6

现有递归CTE查询 #

WITH RECURSIVE tree_view AS (
    SELECT
        id,
        "itemId",
        TYPE,
        "order",
        "learningPath",
        0 AS level
    FROM
        "LearningPathItem"
    WHERE
        "learningPath" = 'cl5y0o4a60273zas89c0yskey'
    UNION ALL
    SELECT
        child.id,
        child."itemId",
        child.type,
        child."order",
        child."learningPath" AS parent,
        level + 1 AS level
    FROM
        "LearningPathItem" child
        JOIN tree_view tv ON child."learningPath" = tv."itemId"
)
SELECT
    tv.type,
    tv."itemId",
    lp.title AS "Learning Path Title",
    es.title AS "Exercise Set Title",
    tv."learningPath" AS parent,
    tv.level,
    tv.order,
    uonlp.efactor AS "Learning Path efactor",
    m.efactor AS "Exercise Set efactor"
FROM
    tree_view tv
    LEFT JOIN "Memoization" m ON m."exerciseSet" = tv."itemId"
        AND m."user" = 'cl524cilt0035d6s8wymsgb4o'
    LEFT JOIN "LearningPath" lp ON lp.id = tv."itemId"
    LEFT JOIN "ExerciseSet" es ON es.id = tv."itemId"
    LEFT JOIN "UserOnLearningPath" uonlp ON uonlp."user" = 'cl524cilt0035d6s8wymsgb4o'
        AND uonlp."learningPath" = tv."itemId"
ORDER BY tv.level, tv.order

当前问题与期望输出 #

现有查询按level+order排序，无法实现嵌套层级的连续排序（子节点会被同层级其他父节点的节点打断）。期望的正确排序效果如下：

Lodash Essentials (set) level 0 order 0
JavaScript Path (path) level 0 order 1
-> JavaScript Essentials (set) level 1 order 0
-> Lodash Essentials 2 (set) level 1 order 1
Root Set (set) level 0 order 2

解决方案：维护排序路径字段 #

在递归CTE中新增sort_path字段，拼接每一层的order值，确保嵌套层级的正确排序：

WITH RECURSIVE tree_view AS (
    SELECT
        id,
        "itemId",
        TYPE,
        "order",
        "learningPath",
        0 AS level,
        -- 用lpad补零避免字符串排序时的数值顺序问题（如10排在2前）
        lpad("order"::text, 4, '0') AS sort_path
    FROM
        "LearningPathItem"
    WHERE
        "learningPath" = 'cl5y0o4a60273zas89c0yskey'
    UNION ALL
    SELECT
        child.id,
        child."itemId",
        child.type,
        child."order",
        child."learningPath" AS parent,
        level + 1 AS level,
        -- 拼接父节点排序路径与当前节点order值
        tv.sort_path || lpad(child."order"::text, 4, '0') AS sort_path
    FROM
        "LearningPathItem" child
        JOIN tree_view tv ON child."learningPath" = tv."itemId"
)
SELECT
    -- 生成带层级前缀的展示文本（可选）
    CASE WHEN level > 0 THEN repeat('-> ', level) ELSE '' END ||
    COALESCE(lp.title, es.title) || ' (' || tv.type || ')' AS display_name,
    tv.level,
    tv."order",
    uonlp.efactor AS "Learning Path efactor",
    m.efactor AS "Exercise Set efactor"
FROM
    tree_view tv
    LEFT JOIN "Memoization" m ON m."exerciseSet" = tv."itemId"
        AND m."user" = 'cl524cilt0035d6s8wymsgb4o'
    LEFT JOIN "LearningPath" lp ON lp.id = tv."itemId"
    LEFT JOIN "ExerciseSet" es ON es.id = tv."itemId"
    LEFT JOIN "UserOnLearningPath" uonlp ON uonlp."user" = 'cl524cilt0035d6s8wymsgb4o'
        AND uonlp."learningPath" = tv."itemId"
-- 关键：按sort_path排序，实现嵌套层级的连续顺序
ORDER BY tv.sort_path

方案说明 #

1. sort_path字段：递归过程中拼接每一层的order值，用lpad补零是为了保证字符串排序时的数值顺序正确性（比如0001 < 0002 < 0010）
2. 排序逻辑：最终按sort_path排序，确保父节点的所有子节点紧跟在父节点之后，同层级内按order排序
3. 展示优化：通过repeat('-> ', level)生成层级前缀，直接输出带嵌套标识的文本，无需后续处理

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