I have the following sample data (items) with some kind of recursion. For the sake of simplicity I limited the sample to 2 level. Matter of fact - they could grow quite deep.

 ---- -------------------------- ---------- ------------------ ------- 
| 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   |
 ---- -------------------------- ---------- ------------------ ------- 

I need to get via SQL all children

• which are not orange
• for a given starting ID

with either starting ID=1. The result should be 1, 1.3.5. When start with ID=4 the should be 2.4.6.

I read little bit and found the CTE should be used. I tried the following simplified definition

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
);

However, this won't even be executed as SQL-statement. I have several question to this:

1. CTE could be used with Hibernate?
2. Is there a way have the result via SQL queries? (more or less as recursive pattern)

I'm somehow lost with the recursive pattern combined with selection of color for the end result.

CodePudding user response：

Your query is invalid for the following reasons:

1. As documented in the manual a recursive CTE requires the RECURSIVE keyword
2. Your JOIN syntax is wrong. You need to remove the , and give the items table an alias.

If you need the color column, just add it to both SELECTs inside the CTE and filter the rows in the final SELECT.

If that is changed, the following works fine:

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'

Note that the column list for the CTE definition is optional, so you can just write with recursive w1 as ....