Get the breadcrumb directory sequence in one query using MySQL

Question

I am supposed to get the breadcrumb or directory hierarchy of a folder based on folder id.

ID   title      parent_id
 1   Home          0
 2   First         1
 3   Second        2
 4   Another       0
 5   Test          4

Based on the table above, if my current folder is Second, then the hierarchy should be Home > First > Second

Hence, my expected result is this:

ID   title      parent_id
 1   Home          0
 2   First         1
 3   Second        2

Case 2: If id is 5, then the expected result is:

 4   Another       0
 5   Test          4

My query below produces up to fourth depth only.

SELECT *
FROM folders AS t1
LEFT JOIN folders AS t2 ON t1.parent_id = t2.id
LEFT JOIN folders AS t3 ON t2.parent_id = t3.id
LEFT JOIN folders AS t4 ON t3.parent_id = t4.id

Is there any other way to generate the full hierarchy up to n depth?

Answer 1

You would use a recursive common table expression for this (in mysql 8 or mariadb 10.2+):

with recursive folder_hierarchy as (
    select f.id,f.title,f.parent_id,0 as level from folder f where f.id=3
    union all
    select pf.id,pf.title,pf.parent_id,level+1 from folder_hierarchy fh join folder f on f.id=fh.id join folder pf on pf.id=f.parent_id
)
select id,title,parent_id from folder_hierarchy
order by level desc

fiddle

However, I would recommend using null instead of 0 to represent no parent.