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