Hibernate-SQL查询:如何获取以特定节点开头的所有子描述符

Question

我有以下带有某种递归的示例数据(items(。为了简单起见，我将样本限制为2级。事实上，它们可以长得很深。

+----+--------------------------+----------+------------------+-------+
| ID |      Item - Name         | ParentID | MaterializedPath | Color |
+----+--------------------------+----------+------------------+-------+
|  1 | Parent 1                 |     null | 1                | green |
|  2 | Parent 2                 |     null | 2                | green |
|  4 | Parent 2 Child 1         |        2 | 2.4              | orange|
|  6 | Parent 2 Child 1 Child 1 |        4 | 2.4.6            | red   | 
|  7 | Parent 2 Child 1 Child 2 |        4 | 2.4.7            | orange|
|  3 | Parent 1 Child 1         |        1 | 1.3              | orange|
|  5 | Parent 1 Child 1 Child   |        3 | 1.3.5            | red   |
+----+--------------------------+----------+------------------+-------+

我需要通过SQL获得所有子

• 不是orange
• 对于给定的起始ID

其中一个起始ID=1。结果应该是1, 1.3.5。当ID=4开始时，应为2.4.6。

我读了一点，发现应该使用CTE。我尝试了以下简化定义

WITH w1( id, parent_item_id) AS 
(       SELECT 
i.id, 
i.parent_item_id
FROM 
item i 
WHERE 
id = 4
UNION ALL 
SELECT 
i.id, 
i.parent_item_id 
FROM 
item, JOIN w1 ON i.parent_item_id = w1.id
);

然而，这甚至不会作为SQL语句执行。我有几个问题：

1. CTE是否可以与Hibernate一起使用
2. 有没有办法通过SQL查询得到结果？(或多或少是递归模式(

我不知何故迷上了递归模式，并选择color作为最终结果。

Answer 1

您的查询无效，原因如下：

1. 如手册中所述，递归CTE需要RECURSIVE关键字
2. 您的JOIN语法错误。您需要删除,，并为items表提供一个别名

如果需要颜色列，只需将其添加到CTE内的两个SELECT中，并在最终SELECT中过滤行即可。

如果更改，则以下操作正常：

WITH recursive w1 (id, parent_item_id, color) AS 
(       
SELECT i.id, 
i.parent_item_id, 
i.color
FROM item i 
WHERE id = 4
UNION ALL 
SELECT i.id, 
i.parent_item_id, 
i.color
FROM item i --<< missing alias
JOIN w1 ON i.parent_item_id = w1.id
)
select *
from w1    
where color <> 'orange'

注意，CTE定义的列列表是可选的，因此您可以只编写with recursive w1 as ....