How to use recursion in MYSQL View-CodePudding

I have a table called categories. In it, there are three columns: id, parentId, and name. The parentId column is a foreign key that references the id column of the same table.

categories

| id | parentId | name      |
|----|----------|-----------|
|  1 |   NULL   | expense   |
|  2 |        1 | food      |
|  3 |        2 | grocery   |
|  4 |        3 | meat      |
|  5 |        3 | fruit     |
|  6 |        2 | dining    |

I want to create a view called categories_view which returns the categories table and adds a column called path which creates a path for that record which is the path of the parent (if the parent is not NULL) and the name of the name.

categories_view

| id | parentId | name      | path                             |
|----|----------|-----------|----------------------------------|
|  1 |   NULL   | expense   | expense                          |
|  2 |        1 | food      | expense > food                   |
|  3 |        2 | grocery   | expense > food > grocery         |
|  4 |        3 | meat      | expense > food > grocery > meat  |
|  5 |        3 | fruit     | expense > food > grocery > fruit |
|  6 |        2 | dining    | expense > food > dining          |

If I were doing this with javascript or something, I could just just recursion, but I'm not sure how to do that in SQL views.

CodePudding user response：

You may use a recursive CTE inside a view:

CREATE VIEW categories_view AS
WITH RECURSIVE cte AS (
    SELECT id, parentId, name, name AS path
    FROM categories
    WHERE parentId IS NULL
    UNION ALL
    SELECT c.id, c.parentId, c.name, CONCAT_WS(' > ', t.path, c.name)
    FROM categories c
    INNER JOIN cte t ON c.parentId = t.id
)

SELECT *
FROM cte
ORDER BY id;

Here is a demo showing that the logic is working.

CodePudding user response：

You can try to use CTE recursive if your Mysql version support it, then use GROUP_CONCAT function to get your path result

Query #1

with recursive cte as (
  select     id,
             parentId,
             name pName,    
             name,
             1 rn
  from       T
  union all
  select     t1.id,
             t1.parentId,
             t1.name pName,
             cte.name ,
             rn   1
  from       T t1
  inner join cte
  on t1.parentId = cte.id
)
select id,
       parentId,
       pName name,
       GROUP_CONCAT(name order by rn desc SEPARATOR '->') path  
from cte
GROUP BY id,parentId,pName;

id	parentId	name	path
1		expense	expense
2	1	food	expense->food
3	2	grocery	expense->food->grocery
4	3	meat	expense->food->grocery->meat
5	3	fruit	expense->food->grocery->fruit
6	2	dining	expense->food->dining

View on DB Fiddle