PostgreSQL反向递归查询：获取评论的根实体ID

可以用PostgreSQL的递归CTE（WITH RECURSIVE）实现反向回溯，从目标评论ID开始逐层向上查找，直到定位到直接关联node的根评论，提取对应的entity_id作为根实体ID。

示例查询语句 #

WITH RECURSIVE comment_ancestors AS (
    -- 初始步骤：获取目标评论的基础信息
    SELECT cid, entity_type, entity_id
    FROM comments
    WHERE cid = 3 -- 替换为你需要查询的评论ID
    UNION ALL
    -- 递归步骤：向上追溯父评论（仅当当前记录是回复评论时继续）
    SELECT c.cid, c.entity_type, c.entity_id
    FROM comments c
    JOIN comment_ancestors ca ON c.cid = ca.entity_id
    WHERE ca.entity_type = 'comment'
)
-- 提取最终关联node的根实体ID
SELECT entity_id AS root_entity_id
FROM comment_ancestors
WHERE entity_type = 'node';

代码说明 #

• 初始查询：先定位到目标评论，获取它的entity_type和entity_id；
• 递归逻辑：如果当前记录是回复评论（entity_type = 'comment'），则通过entity_id关联到父评论的cid，继续向上追溯；
• 最终结果：筛选出递归链中entity_type为node的记录，其entity_id就是所属节点的根实体ID。

如果目标评论本身就是直接关联node的，递归步骤不会执行，直接返回对应的entity_id，结果依然正确。

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