Recursive query in tree MySQL8 / MariaDB10

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

Answer

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

Demo on dbfiddle

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

Demo on dbfiddle