I have this table where you have an ParentID within the same table.

How can I query ALL sorted by Name (Parent First) and child under it. So the result will be like this:

CodePudding user response：

One other way to order the rows in the event the ID value ordering doesn't always align with the aphabetical ordering of the Parent names would be as follows

select *
from t
order by Concat(coalesce((select name from t t2 where t2.id=t.parentId),''), name)

Of course this only works for a single level of parents, which appears to be the case with the sample data.

CodePudding user response：

Obviously the null parent ID means it is a parent and we just need a way to replace the NULL parent ID with the actual ID.

Coalesce allows us to take the first non-null value in a series

So

order by coalesce(parentID, ID), Name

This then will put the parentID first and it not, then ID.

note this slows performance on indexes, so if mySQL supports the concept of function based indexes performance could improve by creating one.

Alternatively; instead of using NULL to denote the parent, use the ID of the parent and when they match it is a parent. (but this can cause looping problems when traversing a hierarchy if not handled)

THis also assumes 1 level of depth. If you have sub levles such as...

 1
 1.2
 1.2.3 <-- 

Then a recursive CTE would be required or similar and we'd have to build out the path for each node and sort by it.

NOTE: All Code content and data from here on is directly from the URL mentioned below. Possible Example: https://www.geeksforgeeks.org/mysql-recursive-cte-common-table-expressions/

In it they give against a 2 column table: (node, Parent)

WITH RECURSIVE
cte ( node, path )
AS
( SELECT node, cast ( 1 as char(30) )  
          FROM bst WHERE parent IS NULL
  UNION ALL
  SELECT bst.node,  CONCAT ( cte.path, '-->', bst.node ) 
         FROM cte JOIN bst ON cte.node = bst.parent
)
SELECT * FROM cte ORDER BY node;

Giving us:

 ------ ----------- 
| node |   path    |
 ------ ----------- 
|  1   | 1         |
|  2   | 1-->2     |
|  3   | 1-->3     |
|  4   | 1-->2-->4 |
|  5   | 1-->2-->5 |
|  6   | 1-->3-->6 |
|  7   | 1-->3-->7 |
 ------ ----------- 

Which would allow you to sort then by the path and name