How To Sort Parents And All Siblings Using Cte For Adjacency List?

This is my table: CREATE TABLE IF NOT EXISTS NODE ( UUID VARCHAR NOT NULL, PARENT_UUID VARCHAR NULL, NAME VARCHAR NOT NULL, PRIMARY KEY (UUID) ); This is my tes

Solution 1:

You can just join the tree to node. The only problem is to keep it sorted according to tree traversal order. Try this, tested in MySql 8.0

EDIT

Now sorting by names-based path, 20 is max name length in the table

WITHRECURSIVE tree (uuid, parent_uuid, name, level, path) AS 
(
    SELECT uuid, parent_uuid, name, 0 level, cast(Rpad(name, 20, ' ') aschar(200)) path
    FROM nodes 
    WHERE uuid ='33d93c3a-1c2d-44b9-8fac-3f83074104a5'UNIONALLSELECT a.uuid, a.parent_uuid, a.name, level-1,  concat(Rpad(a.name, 20, ' '),'>', path)
    FROM nodes a
    INNERJOIN tree b ON b.parent_uuid = a.uuid
)
select uuid, name, level /*, path */from (
   select n.uuid, n.name
      , max(-level) over() + level +1  level
      , concat(substring(first_value(path) over(orderby level), 1, (20+1) * (max(-level) over() + level +1 )), n.name) path
   from tree t
   join nodes n on n.parent_uuid = t.uuid 
       -- no children for starting nodeand t.level <>0unionall--  rootsselect uuid, name, 0, Rpad(name, 20, ' ')
   from nodes
   where parent_uuid isnull 
) t
orderby path

db<>fiddle