首先，cte（WITH RECURSIVE）用于获取主题父项，cte2用于将主题数组转换为行，然后我们将数据连接在一起以获得预期结果。

WITH RECURSIVE topic_parent(id, path) AS (
  SELECT 
    id, ARRAY[id]
  FROM topics 
  WHERE parent = 0
  UNION 
  SELECT 
    t.id, path || t.id
  FROM 
    topics t
    INNER JOIN topic_parent rt ON rt.id = t.parent
),
cte2 as (
  select *, unnest(path) AS linked_id
  from topic_parent
)
select task_id, max(task) as task_name, max(name) as topic_name, linked_id as topic_id
from cte2 c
inner join tasks_topics t on c.id = t.topics_id
inner join tasks t2 on t2.id = t.task_id
inner join topics t3 on t3.id = c.linked_id
group by task_id, linked_id
order by task_id asc, linked_id desc

Demo here

_task_id = 100
topics_from = (
    session.query(Tasks, Topics)
    .join(Topics, Tasks.topics)
    .filter(Tasks.id == _task_id)  # optional
).cte(name="alltopic", recursive=True)

# union or union_all depending on if you want to filter out duplicates (top level)
allq = topics_from.union(
    session.query(Tasks, Topics)
    .filter(Topics.id == topics_from.c.parent_id)
    .filter(Tasks.id == topics_from.c.id)
)

应产生如下结果：

(100, '1+2+3+4', 5, 'sum values', 3)
(100, '1+2+3+4', 6, 'Number sequences', 4)
(100, '1+2+3+4', 3, 'Arithmetics', 1)
(100, '1+2+3+4', 4, 'Progression', 2)
(100, '1+2+3+4', 1, 'Mathematics', 0)
(100, '1+2+3+4', 2, 'Algebra', 1)

通过SQL沿着以下操作：

WITH RECURSIVE alltopic(id, task, id_1, name, parent_id) AS
  (SELECT tasks.id AS id,
          tasks.task AS task,
          topics.id AS id_1,
          topics.name AS name,
          topics.parent_id AS parent_id
   FROM tasks
   JOIN tasks_topics AS tasks_topics_1 ON tasks.id = tasks_topics_1.task_id
   JOIN topics ON topics.id = tasks_topics_1.topics_id
                topics.parent_id AS topics_parent_id
   FROM tasks,
        topics,
        alltopic
   WHERE topics.id = alltopic.parent_id
     AND tasks.id = alltopic.id)
SELECT alltopic.id,
       alltopic.task,
       alltopic.id_1,
       alltopic.name,
       alltopic.parent_id
FROM alltopic