Ordering by criteria - Using Recursive CTE

Question

I have an sql table and I want to order it in the way that every element has the elements who have its code as parentCode in the rows directly beneath. To make it more clear take this example :

            id      name         code               parentCode

parent1      1      "element1"  "parent1code"       null
parent2      2      "element2"  "parent2code"       null
children1    3      "element3"  "children1code"    "parent1Code"
children2    4      "element4"  "children2code"    "parent2Code"
children3    5      "element5"  "children3code"    "parent1Code"

etc ..

and I want to order it this way :

  parent1    
   children1    
   children3 
 parent2   
   children2

PS: there are non determined number of layers in this hierarchy (a child can be also a parent)

Answer 1

This is a bit tricky in MySQL. The basic idea is to build up a path to the top using a recursive CTE and then order by the path. However, you want each identifier in the path to be of constant length to avoid ordering problems. And, MySQL doesn't support arrays, so this all has to go into a string.

So, I would recommend something like this:

with recursive cte as (
      select id, name, code, parent, 
             cast(lpad(id, 4, '0') as char(255)) as path
      from sample
      where parent is null 
      union all
      select s.id, s.name, s.code, s.parent, 
             concat(cte.path, '->', lpad(s.id, 4, '0'))
      from cte join
           sample s 
           on s.parent = cte.code
     )
select *
from cte
order by path;

Here is a db<>fiddle.

Note: This expands the ids to four characters. That can easily be modified.