I have a tree-like structure for categories on my database:
create table cat(
id int auto_increment primary key,
url varchar(50),
parent int references cat(id)
)
How can i build the URL of a category, by concatenating its url with the parent url? (until parent is not null)
I'm using MariaDB 10.2
This recursive CTE will form a URL out of each level in the table, joining them with a / (although that is easily changed). The desired final node is specified in the CTE (in this example, it is 7):
WITH RECURSIVE urls AS (
SELECT url, parent
FROM cat
WHERE id = 7
UNION ALL
SELECT CONCAT_WS('/', cat.url, urls.url), cat.parent
FROM urls
JOIN cat ON cat.id = urls.parent
)
SELECT CONCAT('http://', url) AS url
FROM urls
WHERE parent IS NULL
You can get all of the available paths by omitting the WHERE id = condition from the CTE. For my sample data, that gives:
url
http://home
http://home/apps
http://home/user
http://home/apps/excel
http://home/apps/word
http://home/user/nick
http://home/user/nick/docs
