sql - Postgres递归查询在遍历parent_id时更新字段的值

Question

这是表格

  user_id | parent_id | lft
  --------|-----------|-----
        1 |           | 0
        2 |         1 | 0
        3 |         1 | 0
        4 |         2 | 0

这里是一个查询，从节点 1 开始执行 CTE，遍历 user_id 1 的所有子节点，直到到达叶子节点，并将遍历的子节点 lft 字段的值更新为 1

WITH RECURSIVE d AS (
  SELECT user_id
   FROM btrees
   WHERE user_id = 1
 UNION ALL
  SELECT c.user_id
   FROM d JOIN btrees c ON c.parent_id = d.user_id
)
UPDATE btrees b set lft = 1
 FROM d
 WHERE d.user_id = b.user_id

我只是要求一个相反方向的查询..即。从任何节点到根节点，这样我就可以更新 lft 的值

Answer 1

更新从某个节点开始一直到根的所有节点的查询非常相似:

WITH RECURSIVE d AS (
  SELECT user_id
   FROM btrees
   WHERE user_id = :node_id
 UNION ALL
  SELECT c.user_id
   FROM d JOIN btrees c ON d.parent_id = c.user_id
)
UPDATE btrees b set lft = 1
 FROM d
 WHERE d.user_id = b.user_id

请注意，连接中的条件是相反的。

一般来说，递归查询的工作原理如下:

1. 起始记录集由WITH RECURSIVE 子句中UNION ALL 中的第一个选择确定。
2. UNION ALL 中的第二个选择定义如何从目前找到的记录派生下一级记录。当从上到下遍历时，此查询应该找到所有子项。当从下到上遍历时，应该找到父级。
3. 执行第 2 步，直到在某个迭代中不再添加新记录为止。