MYSQL: recursive procedure that builds a string for each row

Question

I would like to create a procedure that recursively went through a table and built a string for every row. Each row is related to another row except for a few that are at the top of the hierarchy.

Here is what I have:

CREATE TABLE item (
    id              INT         NOT NULL    AUTO_INCREMENT,
    name            VARCHAR(30) NOT NULL,
    category        INT             NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (category)
        REFERENCES item(id)
);

CREATE PROCEDURE get_item()
    SELECT *
    FROM item;

-- main categories --
INSERT INTO item (name, category) VALUES 
    ('groceries',   NULL),
    ('retail',      NULL),
    ('service',     NULL),
    ('grains',       1),
    ('produce',      1),
    ('meats',        1),
    ('dairy',        1),
    ('snacks',       1),
    ('beverages',    1),
    ('car',          2),
    ('home',         2),
    ('clothing',     2),
    ('electronics',  2),
    ('chips',        8), 
    ('dip',          8), 
    ('snack bars',   8), 
    ('haircut',      3);

When get_item() is called it should give this table:

| id | name         | category  |
|----|--------------|-----------|
|  1 | groceries    | NULL      |
|  2 | retail       | NULL      |
|  3 | service      | NULL      |
|  4 | grains       | 1         |
|  5 | produce      | 1         |
|  6 | meats        | 1         |
|  7 | dairy        | 1         |
|  8 | snacks       | 1         |
|  9 | beverages    | 1         |
| 10 | car          | 2         |
| 11 | home         | 2         |
| 12 | clothing     | 2         |
| 13 | electronics  | 2         |
| 14 | chips        | 8         |
| 15 | dip          | 8         |
| 16 | snack bars   | 8         |
| 17 | haircut      | 3         |

I would like it to look like this:

| id | name         | category  | path                              |
|----|--------------|-----------|-----------------------------------|
|  1 | groceries    | NULL      | groceries                         |
|  2 | retail       | NULL      | retail                            |
|  3 | service      | NULL      | service                           |
|  4 | grains       | 1         | groceries > grains                |
|  5 | produce      | 1         | groceries > produce               |
|  6 | meats        | 1         | groceries > meats                 |
|  7 | dairy        | 1         | groceries > dairy                 |
|  8 | snacks       | 1         | groceries > snacks                |
|  9 | beverages    | 1         | groceries > beverages             |
| 10 | car          | 2         | retail > car                      |
| 11 | home         | 2         | retail > home                     |
| 12 | clothing     | 2         | retail > clothing                 |
| 13 | electronics  | 2         | retail > electronics              |
| 14 | chips        | 8         | groceries > snacks > chips        |
| 15 | dip          | 8         | groceries > snacks > dip          |
| 16 | snack bars   | 8         | groceries > snacks > snack bars   |
| 17 | haircut      | 3         | service > haircut                 |

I don't know how I would go about doing this.

Answer 1

Using a stored procedure for this is a PITA:

DROP PROCEDURE get_item;
DELIMITER //
CREATE PROCEDURE get_item(IN p_category INT, IN p_names TEXT)
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE v_id, v_category INT;
  DECLARE v_name VARCHAR(30);
  DECLARE v_path TEXT;
  DECLARE cur CURSOR FOR
    SELECT id, name, category, CONCAT(COALESCE(CONCAT(p_names, ' > '), ''), name) AS path
    FROM item WHERE category <=> p_category;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_id, v_name, v_category, v_path;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT v_id, v_name, v_category, v_path;
    CALL get_item(v_id, v_path);
  END LOOP;
  CLOSE cur;
END//
DELIMITER ;

The result is returned as a series of 17 distinct result-sets, of one row each:

mysql> CALL get_item(NULL, NULL);

+------+-----------+------------+-----------+
| v_id | v_name    | v_category | v_path    |
+------+-----------+------------+-----------+
|    1 | groceries |       NULL | groceries |
+------+-----------+------------+-----------+
1 row in set (0.00 sec)

+------+--------+------------+--------------------+
| v_id | v_name | v_category | v_path             |
+------+--------+------------+--------------------+
|    4 | grains |          1 | groceries > grains |
+------+--------+------------+--------------------+
1 row in set (0.00 sec)

+------+---------+------------+---------------------+
| v_id | v_name  | v_category | v_path              |
+------+---------+------------+---------------------+
|    5 | produce |          1 | groceries > produce |
+------+---------+------------+---------------------+
1 row in set (0.00 sec)

+------+--------+------------+-------------------+
| v_id | v_name | v_category | v_path            |
+------+--------+------------+-------------------+
|    6 | meats  |          1 | groceries > meats |
+------+--------+------------+-------------------+
1 row in set (0.00 sec)

+------+--------+------------+-------------------+
| v_id | v_name | v_category | v_path            |
+------+--------+------------+-------------------+
|    7 | dairy  |          1 | groceries > dairy |
+------+--------+------------+-------------------+
1 row in set (0.01 sec)

+------+--------+------------+--------------------+
| v_id | v_name | v_category | v_path             |
+------+--------+------------+--------------------+
|    8 | snacks |          1 | groceries > snacks |
+------+--------+------------+--------------------+
1 row in set (0.01 sec)

+------+--------+------------+----------------------------+
| v_id | v_name | v_category | v_path                     |
+------+--------+------------+----------------------------+
|   14 | chips  |          8 | groceries > snacks > chips |
+------+--------+------------+----------------------------+
1 row in set (0.01 sec)

+------+--------+------------+--------------------------+
| v_id | v_name | v_category | v_path                   |
+------+--------+------------+--------------------------+
|   15 | dip    |          8 | groceries > snacks > dip |
+------+--------+------------+--------------------------+
1 row in set (0.01 sec)

+------+------------+------------+---------------------------------+
| v_id | v_name     | v_category | v_path                          |
+------+------------+------------+---------------------------------+
|   16 | snack bars |          8 | groceries > snacks > snack bars |
+------+------------+------------+---------------------------------+
1 row in set (0.01 sec)

+------+-----------+------------+-----------------------+
| v_id | v_name    | v_category | v_path                |
+------+-----------+------------+-----------------------+
|    9 | beverages |          1 | groceries > beverages |
+------+-----------+------------+-----------------------+
1 row in set (0.01 sec)

+------+--------+------------+--------+
| v_id | v_name | v_category | v_path |
+------+--------+------------+--------+
|    2 | retail |       NULL | retail |
+------+--------+------------+--------+
1 row in set (0.01 sec)

+------+--------+------------+--------------+
| v_id | v_name | v_category | v_path       |
+------+--------+------------+--------------+
|   10 | car    |          2 | retail > car |
+------+--------+------------+--------------+
1 row in set (0.01 sec)

+------+--------+------------+---------------+
| v_id | v_name | v_category | v_path        |
+------+--------+------------+---------------+
|   11 | home   |          2 | retail > home |
+------+--------+------------+---------------+
1 row in set (0.02 sec)

+------+----------+------------+-------------------+
| v_id | v_name   | v_category | v_path            |
+------+----------+------------+-------------------+
|   12 | clothing |          2 | retail > clothing |
+------+----------+------------+-------------------+
1 row in set (0.02 sec)

+------+-------------+------------+----------------------+
| v_id | v_name      | v_category | v_path               |
+------+-------------+------------+----------------------+
|   13 | electronics |          2 | retail > electronics |
+------+-------------+------------+----------------------+
1 row in set (0.02 sec)

+------+---------+------------+---------+
| v_id | v_name  | v_category | v_path  |
+------+---------+------------+---------+
|    3 | service |       NULL | service |
+------+---------+------------+---------+
1 row in set (0.02 sec)

+------+---------+------------+-------------------+
| v_id | v_name  | v_category | v_path            |
+------+---------+------------+-------------------+
|   17 | haircut |          3 | service > haircut |
+------+---------+------------+-------------------+
1 row in set (0.02 sec)

When calling this in application code, you need to loop over it as a multi-result-set statement.

I hardly ever use stored procedures in MySQL. This would be a lot simpler in application code.

I also prefer to use alternative methods to query hierarchical data sets, not using recursion. Fortunately in MySQL 8.0, you have recursive queries (like we already do in nearly every other SQL database). You can do this in MySQL 8.0 without using a stored procedure:

WITH RECURSIVE MyTree AS (
    SELECT id, name, category, name AS path FROM item WHERE category IS NULL
    UNION ALL
    SELECT i.id, i.name i.category, CONCAT(t.path, ' > ', i.name)
    FROM item AS i JOIN MyTree AS t ON i.category = t.id
)
SELECT * FROM MyTree;