如何在JOIN语句中使用递归CTE实现订阅论坛父级查询

核心思路 #

不再依赖heritage字符串模糊匹配，而是从用户直接订阅的所有论坛作为起始节点，通过parentID递归向上遍历所有父级论坛，同时保留订阅标记逻辑。

完整递归CTE查询（含排序优化） #

WITH RECURSIVE forum_hierarchy AS (
    -- 起始节点：获取用户所有直接订阅的论坛
    SELECT
        f.forumID,
        f.title,
        f.parentID,
        f.`order`,
        s.ID AS subbed_forumID,
        1 AS level  -- 标记层级：订阅论坛本身为层级1，父级依次递增
    FROM forumSubs s
    INNER JOIN forums f ON s.ID = f.forumID
    WHERE s.userID = {$userID}
      AND s.`type` = 'f'
      AND f.forumID != 0  -- 排除根节点（若parentID=0对应根）

    UNION ALL

    -- 递归遍历：向上获取当前节点的父级论坛
    SELECT
        p.forumID,
        p.title,
        p.parentID,
        p.`order`,
        fh.subbed_forumID,  -- 传递原始订阅论坛ID，用于标记isSubbed
        fh.level + 1 AS level
    FROM forums p
    INNER JOIN forum_hierarchy fh ON p.forumID = fh.parentID
    WHERE p.forumID != 0
)
-- 最终输出：标记是否为用户直接订阅的论坛，并按层级排序
SELECT
    forumID,
    title,
    parentID,
    `order`,
    CASE WHEN forumID = subbed_forumID THEN 1 ELSE 0 END AS isSubbed
FROM forum_hierarchy
ORDER BY level DESC, `order`;  -- 层级越高（越接近根节点）越靠前，和原查询LENGTH(heritage)效果一致

关键细节说明 #

1. 起始节点：直接关联forumSubs和forums，拿到用户订阅的所有论坛，同时记录原始订阅ID（subbed_forumID）。
2. 递归逻辑：通过parentID关联父级论坛，传递subbed_forumID确保所有父级节点都能关联到原始订阅记录。
3. 订阅标记：用CASE判断当前论坛ID是否等于原始订阅ID，实现isSubbed字段的逻辑。
4. 排序优化：新增level字段替代原查询的LENGTH(heritage)，避免字符串计算，性能更优。

防循环增强版（可选） #

如果担心数据存在循环引用（比如parentID指向子节点），可以添加路径字段避免死循环：

WITH RECURSIVE forum_hierarchy AS (
    SELECT
        f.forumID,
        f.title,
        f.parentID,
        f.`order`,
        s.ID AS subbed_forumID,
        1 AS level,
        CONCAT(',', f.forumID, ',') AS path  -- 记录遍历路径
    FROM forumSubs s
    INNER JOIN forums f ON s.ID = f.forumID
    WHERE s.userID = {$userID}
      AND s.`type` = 'f'
      AND f.forumID != 0

    UNION ALL

    SELECT
        p.forumID,
        p.title,
        p.parentID,
        p.`order`,
        fh.subbed_forumID,
        fh.level + 1 AS level,
        CONCAT(fh.path, p.forumID, ',') AS path
    FROM forums p
    INNER JOIN forum_hierarchy fh ON p.forumID = fh.parentID
    WHERE p.forumID != 0
      AND NOT fh.path LIKE CONCAT('%,', p.forumID, ',%')  -- 跳过已遍历的节点
)
SELECT
    forumID,
    title,
    parentID,
    `order`,
    CASE WHEN forumID = subbed_forumID THEN 1 ELSE 0 END AS isSubbed
FROM forum_hierarchy
ORDER BY level DESC, `order`;

优势对比 #

• 性能：基于parentID主键关联，比LIKE字符串匹配效率更高，尤其数据量大时。
• 可靠性：不依赖heritage字段的格式正确性，避免字符串拼接/匹配的潜在错误。
• 扩展性：方便新增层级相关的业务逻辑（比如显示层级深度）。

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