How can fetch data like a tree structure in mysql?

Question

If I search Pro I need to get data like Electronics > Mobile > Iphone > Pro.

That means,

Pro parent_id is 3 sibling_order 3 is Iphone.

Iphone parent_id is 2 sibling_order 2 is Mobile.

Mobile parent_id is 1 sibling_order 1 is Electronics.

Another example:

If I search Mobile then I need to get data like Electronics/Mobile.

I tried recursive query but it is not working Syntax error.

WITH RECURSIVE category_path (id, name, path) AS
(
  SELECT id, name, name as path
    FROM categories
    WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, CONCAT(cp.path, ' > ', c.name)
    FROM category_path AS cp JOIN categories AS c
      ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY path;

Any help would be appreciated.

id  name              sibling_order  parent_id

1   Electronics             1         NULL
2   Mobile                  2          1
3   Iphone                  3          2
4   Pro                     4          3
5   SE                      5          3
6   XR                      6          3
7   Samsung                 7          2
8   Galaxy                  8          8
9   Note                    9          8
10  Home & Furniture        10        NULL
11  Kitchen Storage         11        11
12  Lunch Box               12        12
13  Living Room Furniture   13        11
14  Sofa                    14        13

What does `SELECT @@version;` report? If it's less than MySQL 8.0, then recursive queries aren't supported. — Bill Karwin, Nov 23 '20 at 20:52
You might like my answer to [What is the most efficient/elegant way to parse a flat table into a tree?](https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree) or my presentation [Recursive Query Throwdown](https://www.slideshare.net/billkarwin/recursive-query-throwdown). — Bill Karwin, Nov 23 '20 at 20:53
Hierarchical data can be done with [CTE](https://www.mysqltutorial.org/mysql-recursive-cte/) - see reportsTo example. — Joop Eggen, Nov 23 '20 at 21:32

score 1 · Accepted Answer · answered Nov 23 '20 at 21:27

I solved it by creating a function that calls a recursive procedure

DROP PROCEDURE IF EXISTS `spCategoryPath`;
DELIMITER //
CREATE PROCEDURE `spCategoryPath`(IN `categoryID` INT UNSIGNED, OUT `return_path` TEXT)
BEGIN
    DECLARE categoryName VARCHAR(50);
    DECLARE categoryParentId INT;
    DECLARE pathResult TEXT;
    SET max_sp_recursion_depth=50;

    SELECT name, parent_id INTO categoryName, categoryParentId FROM categories WHERE id=categoryID;

    IF ISNULL(categoryParentId) THEN
        SELECT categoryName INTO return_path;
    ELSE
        CALL spCategoryPath(categoryParentId, pathResult);
        SELECT CONCAT(pathResult, ' > ', categoryName) INTO return_path;
    END IF;
END //
DELIMITER ;


DROP FUNCTION IF EXISTS `fnCategoryPath`;
DELIMITER //
CREATE FUNCTION fnCategoryPath(`categoryID` INT) RETURNS TEXT DETERMINISTIC
BEGIN
       DECLARE pathResult TEXT;
       CALL spCategoryPath(categoryID, pathResult);
       RETURN pathResult;
END //
DELIMITER ;

The function is executed like this:

SELECT fnCategoryPath(6);

Generating the following result:
Electronics > Mobile > Iphone > XR

How can fetch data like a tree structure in mysql?

1 Answers1