跨两表使用递归CTE查找层级顶端的问题排查

原查询的问题 #

你的递归CTE无法运行的核心原因有两个：

1. 无限递归循环：递归部分的关联条件t.parent = c.parent会导致每次递归都匹配所有具有相同父节点的记录，无法终止递归过程，最终导致查询因超出递归深度或资源耗尽而终止。
2. 锚点查询遗漏顶层节点：锚点只关联了relationships表的数据，漏掉了没有父节点的顶层对象（objectid=120）。

正确的递归CTE实现 #

要获取每个文档的顶层关联对象，需要从每个节点向上递归追溯父节点，直到找到没有父节点的顶层对象，具体实现如下：

WITH recursive_cte AS (
    -- 锚点：所有文档，顶层节点的topcontainer是自身，非顶层节点先记录父节点
    SELECT 
        d.objectid,
        r.parent,
        CASE WHEN r.parent IS NULL THEN d.objectid ELSE NULL END AS topcontainer
    FROM documents d
    LEFT JOIN relationships r ON d.objectid = r.objectid
    
    UNION ALL
    
    -- 递归：向上追溯父节点，直到找到顶层
    SELECT 
        rc.objectid,
        r.parent,
        CASE WHEN r.parent IS NULL THEN rc.parent ELSE NULL END AS topcontainer
    FROM recursive_cte rc
    JOIN relationships r ON rc.parent = r.objectid
    WHERE rc.topcontainer IS NULL -- 只处理还没找到顶层的节点
)
-- 提取每个节点的顶层容器，优先取已找到的topcontainer，否则用最终的parent（即顶层）
SELECT 
    objectid,
    COALESCE(topcontainer, parent) AS topcontainer
FROM recursive_cte
WHERE topcontainer IS NOT NULL OR parent IS NULL -- 保留顶层节点及已找到顶层的节点
ORDER BY objectid;

更简洁的替代写法 #

可以在递归过程中直接传递顶层容器，直到无法继续递归：

WITH recursive_cte AS (
    -- 锚点：所有文档，初始topcontainer为自身，如果有父节点则后续递归更新
    SELECT 
        d.objectid,
        d.objectid AS topcontainer,
        r.parent
    FROM documents d
    LEFT JOIN relationships r ON d.objectid = r.objectid
    
    UNION ALL
    
    -- 递归：用父节点的顶层容器更新当前节点的topcontainer
    SELECT 
        rc.objectid,
        r_top.topcontainer,
        r.parent
    FROM recursive_cte rc
    JOIN relationships r ON rc.parent = r.objectid
    JOIN recursive_cte r_top ON r.objectid = r_top.objectid
    WHERE rc.parent IS NOT NULL -- 只处理还有父节点的节点
)
-- 每个节点只保留最终的顶层容器记录
SELECT DISTINCT ON (objectid)
    objectid,
    topcontainer
FROM recursive_cte
ORDER BY objectid, (parent IS NOT NULL) DESC; -- 优先取parent为NULL的记录（即顶层）

结果验证 #

以上两种查询都会返回你预期的结果：

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