sql - 如何跟踪 PostgreSQL 中的顶级文件夹？

Question

我正在存储一个树结构，更准确地说是一个folder结构，作为 PostgreSQL 表。

是这样的：

folders(folder_id - UUID, folder_parent_id - UUID: foreign key folders.folder_id, folder_name - string)

我使用公用表表达式 (CTE) 和递归查询进行导航。

但是，我想执行一些“每个子树”处理，为此我想我可以有一个查询，其中每个文件夹还存储其顶级父级。

想想这样的事情：

A -> B -> C
  -> D -> E

我希望返回的行看起来像这样：

folder_id = A, folder_parent_id = A, folder_top_level_parent_id = A
folder_id = B, folder_parent_id = A, folder_top_level_parent_id = A
folder_id = C, folder_parent_id = B, folder_top_level_parent_id = A
folder_id = D, folder_parent_id = A, folder_top_level_parent_id = A
folder_id = E, folder_parent_id = D, folder_top_level_parent_id = A

返回这个的查询会是什么样子？

标签： sqlpostgresqlcommon-table-expression

Answer 1

演示：db<>小提琴

这是一个直接的递归 CTE。只需将顶级元素添加到您的输出并将其保存在递归中：

WITH RECURSIVE cte AS (
    SELECT 
        folder_id, 
        folder_parent_id, 
        folder_id AS folder_top_level_parent_id
    FROM folders 
    WHERE folder_id = folder_parent_id
    
    UNION 
    
    SELECT
        f.folder_id,
        f.folder_parent_id,
        c.folder_top_level_parent_id
    FROM folders f
    JOIN cte c ON f.folder_parent_id = c.folder_id
)
SELECT * FROM cte