R2DBC邻接列表获取所有子级

Question

我有一个表，它有id和parentId列：我把这个结构称为邻接列表<所以，现在我想得到任意id的所有子项。这个问题的经典解决方案是使用递归，例如这里是Postgres过程或CTE实现。

我目前正在使用Spring Webflux和Spring Data R2DBC Postgres R2DBC驱动程序（它还不支持存储过程）<我如何以被动的方式处理这个问题？有没有可能，或者我遗漏了一些概念上的错误？

UPD 1:

+-------------+---------+
|id           |parent_id|
+-------------+---------+
|root         |NULL     |
|id1          |root     |
|dir1         |root     |
|dir1_id1     |dir1     |
|dir1_dir1    |dir1     |
|dir1_dir1_id1|dir1_dir1|
+-------------+---------+

现在，我想在ReactiveCrudRepository中有一个方法，它将返回所提供id的所有子级。

例如，使用示例html" target="_blank">数据：通过提供id='dir1'，我希望获得id为：['dir1\u id1'，“dir1\u dir1”，“dir1\u dir1\u id1”的子级。

Answer 1

我认为最好的sql方法是递归CTE（公共表表达式），您尝试过吗？我从来没有试过多排。

WITH recursive nodes AS (
   SELECT id, parent_id
   FROM t
   WHERE parent_id = 'dir1'
   
   UNION ALL
   
   SELECT t.id, t.parent_id
   FROM nodes n
   INNER JOIN t ON t.parent_id = n.id
)

SELECT id 
FROM nodes;

parent\u id='dir1'的输出

Answer 2

使用proc或cte与完全扫描无关。

在您的案例场景中，您只需要使用递归cte，但在id、parentid上添加索引肯定会有所帮助

create index idx_name on tablename (parentid , id);

此外，10k行并没有那么大，索引肯定会大大提高cte。