Postgres递归查询以遍历parent_id时更新字段的值 [英] Postgres recursive query to update values of a field while traversing parent_id

问题描述

这是表

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

这里是从节点1执行CTE并遍历user_id 1的所有子项直到到达一片叶子并将行进的儿童lft字段的值更新为1

Here is a query to do a CTE from node 1 and traverse all the children of user_id 1 until a leaf is reached and update the value of the travesed chidren lft field to 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的值

I am just asking for a query that will go in the opposite direction .. ie. from any node to the root node so I can update the value of lft

推荐答案

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

The query which updates all nodes starting from some node and up to the root is very similar:

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

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

Note that condition in join is reversed.

一般而言，递归查询的工作方式如下：

In general recursive queries work as follows:

1. 记录的起始集合由WITH RECURSIVE子句中UNION ALL中的第一个选择确定。


2. UNION ALL中的第二个选择定义如何从中导出下一级记录到目前为止找到的记录。从上到下遍历时，此查询应查找所有子级。从下到上遍历时，应该找到父级。


3. 执行步骤2，直到在某个迭代中没有添加新记录为止。

这篇关于Postgres递归查询以遍历parent_id时更新字段的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！