How to use recursion in MYSQL View

Question

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 use recursion, but I'm not sure how to do that in SQL views.

Answer 1

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.