CODEWITHSUNDEEP
sqlpostgresql
Volodymyr Lapan
Volodymyr Lapan

Reputation: 101

Query nodes above given record in tree

I have table

               Table "public.menu_items"
   Column    |  Type   | Collation | Nullable | Default 
-------------+---------+-----------+----------+---------
 id          | integer |           | not null | 
 name        | text    |           |          | 
 parent_id   | integer |           |          | 
 position    | integer |           |          | 
 is_category | boolean |           |          |

And I have this tree of records

Lesson1 (id: 1, parent_id: null, position: 1, is_category: false)
Category2 (id: 2, parent_id: null, position: 2, is_category: true)
|_ Lesson2.1 (id: 5, parent_id: 2, position: 1, is_category: false)
|_ Category2.2 (id: 6, parent_id: 2, position: 2, is_category: true)
   |_ Lesson2.2.1 (id: 8, parent_id: 6, position: 1, is_category: false)
   |_ Lesson2.2.2 (id: 9, parent_id: 6, position: 2, is_category: false)
   |_ Lesson2.2.3 (id: 10, parent_id: 6, position: 3, is_category: false)
   |_ Lesson2.2.4 (id: 11, parent_id: 6, position: 4, is_category: false)
|_ (Lesson2.3 (id: 7, parent_id: 2, position: 3, is_category: false)
Lesson3 (id: 3, parent_id: null, position: 3, is_category: false)
Category4 (id: 4, parent_id: null, position: 4, is_category: true)

I know id and position of record for example id = 10, position = 3. (Lesson2.2.3)

And I need to query All lessons(is_category = false) above given record in tree. In our case

Lesson1 (id: 1, parent_id: null, position: 1, is_category: false)
Lesson2.1 (id: 5, parent_id: 2, position: 1, is_category: false)
Lesson2.2.1 (id: 8, parent_id: 6, position: 1, is_category: false)
Lesson2.2.2 (id: 9, parent_id: 6, position: 2, is_category: false)

I probably need to use WITH RECURSIVE for this. How to write such a query?

UPDATE Now my query looks like this, but it fetches only ancestors, I probably need nested WITH RECURSIVE to fetch children of each ancestor.

WITH RECURSIVE r AS
  (SELECT *
   FROM menu_items
   WHERE parent_id =
       (SELECT parent_id
        FROM menu_items
        WHERE id = 10)
     AND POSITION < 3
   UNION SELECT x.*
   FROM menu_items x
   JOIN r ON x.id = r.parent_id
   OR (x.parent_id IS NULL
       AND r.parent_id IS NULL
       AND x.position <
         (SELECT POSITION
          FROM menu_items
          WHERE id = r.id)))
SELECT *
FROM r;

Upvotes: 0

Views: 66

Answers (1)

S-Man
S-Man

Reputation: 23676

Disclaimer: I am not quiet sure if I got your use case completely (see comments under the question). But in general this is a solution. Maybe you should calibrate some little things...

demo:db<>fiddle

WITH RECURSIVE rec_lessons AS (
    SELECT 
        id,
        position,
        name,
        parent_id,
        position::text as path,
        is_category
    FROM
        lessons
    WHERE parent_id IS NULL

    UNION ALL

    SELECT 
        l.id,
        l.position,
        l.name,
        l.parent_id,
        rl.path || '.' || l.position,
        l.is_category
    FROM
        lessons l
    JOIN rec_lessons rl ON l.parent_id = rl.id
)
SELECT * 
FROM rec_lessons
WHERE is_category = false

A recursive CTE always consists of two parts divided by the UNION clause. The first query is the starting point, the second one the recursion part.

At the start we need to find all rows that may be your tree roots. I assumed that this would be all with no parent.

For the recursion we query all rows from the original table which have as parent_id the id of the previous turn. That's what the join does. So at start all roots have the ids (1,2,3,4). In the first recursion we find all rows where the parent_ids are one of (1,2,3,4). That give us the rows with the ids (5,6,7). These rows are united to the start result with UNION ALL. The next step is to find all rows with parent_ids are one of (5,6,7) and so on.

I added the path which can describe the tree. This is expanded in every turn.

Finally (outside the WITH clause) I filter all non-categories.

Upvotes: 1

Related Questions