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?

my problem is that i have indefinite number of ancestors i.e movies >> sci-fiction >> space >> saturn >> anothercategory >> dummy category etc... — Andrew Naem, Jan 12 '22 at 22:32
no the approach is using case when beside order by statement , but this works if only i have only sub-categories, and then sub-sub-categories will not be ordered correctly — Andrew Naem, Jan 12 '22 at 22:42
https://stackoverflow.com/questions/17147500/mysql-sort-category-according-to-parent-id — Andrew Naem, Jan 12 '22 at 22:43

score 2 · Answer 1 · answered Jan 12 '22 at 23:26

2

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:

answered Jan 12 '22 at 23:26

Bill Karwin

538,548
86
673
828

is there any solution without using recursive CTE ? it's not possible for me to upgrade my MariaDB version now – Andrew Naem Jan 13 '22 at 20:22
Not without storing the data differently. See the links I provided. – Bill Karwin Jan 13 '22 at 21:24
by the way i am using phpmyadmin in which the mentioned mariaDB version, this for local development , and i am wondering because i installed wordpress (for another project ) and used the same phpmyadmin to manage wordpress database , and i found that wordpress can classify categories as i mentioned but with the same version of mariaDB in phpmyadmin. so how wordpress could do this without using recursive CTE ? – Andrew Naem Jan 14 '22 at 08:07
Why don't you look in the code and find out. It sounds like it is on your computer, not mine. – Bill Karwin Jan 14 '22 at 14:28
ok thanks dear for your time and patience – Andrew Naem Jan 14 '22 at 20:36

score 1 · Answer 2 · answered Jan 12 '22 at 23:36

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

is there any solution without using recursive CTE ? it's not possible for me to upgrade my MariaDB version now – — Andrew Naem, Jan 13 '22 at 20:23
See the comment from @Bill Karwin. I should have looked closer at the MariaDB version that you were using, but yeah, I don't know how to get the solution you're looking for without upgrading or changing your data structure. — DonkeyKongII, Jan 13 '22 at 21:35

How to classify categories according their parent id using sql

2 Answers2