MySQL 5.0 hierarchical recursive search

Question

I have a table like this

CREATE TABLE IF NOT EXISTS `categories` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(150) NOT NULL,
    `parent_id` INT(11) NULL,
    `slug` VARCHAR(150) NOT NULL,
    PRIMARY KEY (`id`),
)

This is an example of the table as how it is currently:

id      name               parent_id     slug
----------------------------------------------------
1       Books              NULL          books
2       Anthology          1             anthology
3       Classic            1             classic
4       Drama              1             drama
5       Fable              1             fable
6       Aesop              5             aesop
7       Bidpai             5             bidpai
8       Stephen King       2             stephen-king
9       Magazines          NULL          magazines
10      Lifestyle          9             lifestyle
11      Wellness           9             wellness
12      Spa                11            spa

Note: i am on an old version of MySQL (5.0) so i cannot use recursive functions unfortunately

I want to select all parents and include them into the search result as well to get a nice overview of all the categories with the correct slugs in my application like this:

id      name            id_route     slug
----------------------------------------------------
1       Books           1            books
2       Anthology       1,2          books/anthology
3       Classic         1,3          books/classic
4       Drama           1,4          books/drama
5       Fable           1,5          books/fable
6       Aesop           1,5,6        books/fable/aesop
7       Bidpai          1,5,7        books/fable/bidpai
8       Stephen King    1,2,8        books/anthology/stephen-king
9       Magazines       9            magazines
10      Lifestyle       9,10         magazines/lifestyle
11      Wellness        9,11         magazines/wellness
12      Spa             9,11,2       magazines/wellness/spa

Is it possible to retrieve a result like this in the first place? Because i am breaking my head around this at the moment. Any help or a push in the right direction would be amazing!

Answer 1

The following solution will work as far as you do NOT have more than three levels of hierarchy:

SELECT  IF(level3_id IS NOT NULL, level3_id, IF(level2_id IS NOT NULL, level2_id, level1_id)) AS id,
        IF(level3_name IS NOT NULL, level3_name, IF(level2_name IS NOT NULL, level2_name, level1_name)) AS name,
        CONCAT_WS(",", level1_id, level2_id,level3_id) AS id_route,
        CONCAT_WS("/", level1_slug, level2_slug,level3_slug) AS slug
FROM (
        SELECT  level1.id AS level1_id, level1.name AS level1_name, level1.slug AS level1_slug,
                level2.id AS level2_id, level2.name AS level2_name, level2.slug AS level2_slug,
                level3.id AS level3_id, level3.name AS level3_name, level3.slug AS level3_slug
        FROM categories AS level1
        RIGHT JOIN categories AS level2 ON level1.id = level2.parent_id
        RIGHT JOIN categories AS level3 ON level2.id = level3.parent_id
    ) AS levels
ORDER BY id;

Sadly such solution will not scale up well. Recursive CTE is the better solution so you should consider upgrading.

Answer 2

CREATE PROCEDURE GetTree()
BEGIN
CREATE TABLE tmp_cat LIKE categories;
ALTER TABLE tmp_cat ADD COLUMN path TEXT;
INSERT INTO tmp_cat (id, name, parent_id, slug, path)
    SELECT id, name, parent_id, slug, name 
    FROM categories
    WHERE parent_id IS NULL;
REPEAT
    INSERT IGNORE INTO tmp_cat (id, name, parent_id, slug, path)
        SELECT categories.id, categories.name, categories.parent_id, categories.slug, CONCAT(tmp_cat.path, ',', categories.name)
        FROM categories
        JOIN tmp_cat ON tmp_cat.id = categories.parent_id;
UNTIL ROW_COUNT() = 0
END REPEAT;
SELECT * FROM tmp_cat;
DROP TABLE tmp_cat;
END

fiddle