时间戳

首页 > 解决方案 > Query nodes above given record in tree

sql - Query nodes above given record in tree

问题描述

I have table

               Table "public.menu_items"
   Column    |  Type   | Collation | Nullable | Default 
-------------+---------+-----------+----------+---------
 id          | integer |           | not null | 
 name        | text    |           |          | 
 parent_id   | integer |           |          | 
 position    | integer |           |          | 
 is_category | boolean |           |          |

And I have this tree of records

Lesson1 (id: 1, parent_id: null, position: 1, is_category: false)
Category2 (id: 2, parent_id: null, position: 2, is_category: true)
|_ Lesson2.1 (id: 5, parent_id: 2, position: 1, is_category: false)
|_ Category2.2 (id: 6, parent_id: 2, position: 2, is_category: true)
   |_ Lesson2.2.1 (id: 8, parent_id: 6, position: 1, is_category: false)
   |_ Lesson2.2.2 (id: 9, parent_id: 6, position: 2, is_category: false)
   |_ Lesson2.2.3 (id: 10, parent_id: 6, position: 3, is_category: false)
   |_ Lesson2.2.4 (id: 11, parent_id: 6, position: 4, is_category: false)
|_ (Lesson2.3 (id: 7, parent_id: 2, position: 3, is_category: false)
Lesson3 (id: 3, parent_id: null, position: 3, is_category: false)
Category4 (id: 4, parent_id: null, position: 4, is_category: true)

I know id and position of record for example id = 10, position = 3. (Lesson2.2.3)

And I need to query All lessons(is_category = false) above given record in tree. In our case

Lesson1 (id: 1, parent_id: null, position: 1, is_category: false)
Lesson2.1 (id: 5, parent_id: 2, position: 1, is_category: false)
Lesson2.2.1 (id: 8, parent_id: 6, position: 1, is_category: false)
Lesson2.2.2 (id: 9, parent_id: 6, position: 2, is_category: false)

I probably need to use WITH RECURSIVE for this. How to write such a query?

UPDATE Now my query looks like this, but it fetches only ancestors, I probably need nested WITH RECURSIVE to fetch children of each ancestor.

WITH RECURSIVE r AS
  (SELECT *
   FROM menu_items
   WHERE parent_id =
       (SELECT parent_id
        FROM menu_items
        WHERE id = 10)
     AND POSITION < 3
   UNION SELECT x.*
   FROM menu_items x
   JOIN r ON x.id = r.parent_id
   OR (x.parent_id IS NULL
       AND r.parent_id IS NULL
       AND x.position <
         (SELECT POSITION
          FROM menu_items
          WHERE id = r.id)))
SELECT *
FROM r;

标签： sqlpostgresql

解决方案

免责声明：我不确定我是否完全了解了您的用例（请参阅问题下的评论）。但总的来说，这是一个解决方案。也许你应该校准一些小东西......

演示：db<>小提琴

WITH RECURSIVE rec_lessons AS (
    SELECT 
        id,
        position,
        name,
        parent_id,
        position::text as path,
        is_category
    FROM
        lessons
    WHERE parent_id IS NULL

    UNION ALL

    SELECT 
        l.id,
        l.position,
        l.name,
        l.parent_id,
        rl.path || '.' || l.position,
        l.is_category
    FROM
        lessons l
    JOIN rec_lessons rl ON l.parent_id = rl.id
)
SELECT * 
FROM rec_lessons
WHERE is_category = false

递归 CTE 总是由由UNION子句划分的两部分组成。第一个查询是起点，第二个是递归部分。

一开始我们需要找到所有可能是你的树根的行。我以为这一切都没有父母。

对于递归，我们查询原始表中的所有行，其中 parent_id 为上一轮的 id。这就是join的作用。所以一开始所有的根都有ids (1,2,3,4). 在第一次递归中，我们找到所有行其中parent_ids是 之一(1,2,3,4)。这给了我们带有ids (5,6,7). 这些行用 联合到开始结果UNION ALL。下一步是查找所有包含parent_ids其中之一的行(5,6,7)，依此类推。

我添加了可以描述树的路径。这在每一个回合中都会被扩展。

最后（在WITH子句之外）我过滤了所有非类别。

推荐阅读