PostgreSQL中任务表与层级主题表的关联递归查询实现

在PostgreSQL数据库中，Topic与Task表为多对多关系，通过关联表tasks_topics连接，具体表结构与数据如下：

1. Topic表（主题表） #

包含id、name、parent三列，为层级结构，数据如下：

2. Task表（任务表） #

包含id、task两列，数据如下：

3. 关联表tasks_topics #

数据如下：

需要将任务表与主题表的递归查询结果关联，得到包含四列的结果集：task_id、任务文本、主题父节点名称、主题父节点id，预期结果如下：

目前已实现单个主题的递归查询，代码如下：

WITH RECURSIVE topic_parent AS (
  SELECT 
    id, 
    name, 
    parent 
  FROM 
    topics 
  WHERE 
    id = 3 
  UNION 
  SELECT 
    topics.id, 
    topics.name, 
    topics.parent 
  FROM 
    topics 
    INNER JOIN topic_parent ON topic_parent.parent = topics.id
) 
SELECT 
  * 
FROM 
  topic_parent

但不知道如何将该递归查询与任务表通过id关联，求解决方案。

可以通过将关联表tasks_topics与递归CTE结合，先获取每个任务关联的所有主题及其父节点链，再关联任务表获取任务文本。完整SQL代码如下：

WITH RECURSIVE task_topic_hierarchy AS (
    -- 起始节点：任务直接关联的主题
    SELECT
        tt.task_id,
        t.id AS topics_id,
        t.name AS topics_name,
        t.parent
    FROM tasks_topics tt
    JOIN topics t ON tt.topics_id = t.id
    
    UNION ALL
    
    -- 递归获取所有父节点
    SELECT
        tth.task_id,
        t.id AS topics_id,
        t.name AS topics_name,
        t.parent
    FROM task_topic_hierarchy tth
    JOIN topics t ON tth.parent = t.id
    WHERE t.parent != 0 -- 排除根节点的无效父节点（0）
)
SELECT
    tth.task_id,
    tk.task AS name,
    tth.topics_name,
    tth.topics_id
FROM task_topic_hierarchy tth
JOIN tasks tk ON tth.task_id = tk.id
ORDER BY tth.task_id, tth.topics_id DESC;

逻辑说明： #

1. 递归CTE task_topic_hierarchy：
   • 起始部分：从关联表tasks_topics拿到每个任务关联的直接主题，同时保留任务ID、主题ID、名称和父节点信息。
   • 递归部分：基于上一层的父节点ID，循环查询对应的父主题，直到父节点为0（根节点的父节点）时停止。
2. 关联任务表：将递归得到的层级数据与tasks表关联，获取对应的任务文本。
3. 排序：按任务ID和主题ID降序排列，与预期结果的顺序匹配。

内容的提问来源于stack exchange，提问作者Владимир Кузовкин