How to classify categories according their parent id using sql

Question

Suppose I have the following table

id	category_name	parent_id
1	movies	0
2	technology	0
3	sci-fiction	1
4	romantic	1
5	documentries	0
6	space	3
7	heros	3
8	saturn	6
9	mars	6
10	black holes	6

What I want is order these items in a way that every parent followed by his childs like this:

id	category_name	parent_id
1	movies	0
3	sci-fiction	1
6	space	3
8	saturn	6
9	mars	6
7	heros	3
4	romantic	1
2	technology	0
5	documentries	0
10	black holes	6

How to write a query to perform action like this?

Answer 1

Recursive CTE expressions are supported in MariaDB 10.2. You tagged your question mariadb-10.1 so I think you'll have to upgrade if you want to stay with MariaDB.

If you can store your data differently, you could use one of the alternative ways of storing hierarchical data that became popular for MySQL or MariaDB before they supported recursive CTE expressions. For some suggestions for this, see:

• What is the most efficient/elegant way to parse a flat table into a tree?
• What are the options for storing hierarchical data in a relational database?

Answer 2

I think a recursive CTE is your best bet here as suggested by @Dale K. I think the recursive CTE that I put together here is pretty close to what you'd want. Let me know what you think:

WITH RECURSIVE cte_stuff AS (
    (SELECT      
        id, 
        category_name,
        parent_id,
       CAST(category_name AS VARCHAR(5000)) as hierarchy
    FROM       
        stuff st
    WHERE parent_id = 0)
    UNION ALL
    SELECT 
        s.id,
        s.category_name,
        s.parent_id,
        CAST(CONCAT(ss.hierarchy,'->',s.category_name) AS VARCHAR(5000)) AS Hierarchy
    FROM 
        stuff s
        JOIN cte_stuff ss 
            ON ss.id = s.parent_id
)
SELECT * FROM cte_stuff ORDER by hierarchy

Results look like this:

You can see my dbfiddle here: https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=b5c0b669f0bc611bda576502967170ee