Find Path for Nested Categories in MySQL

Introduction

This tutorial shows how to find the entire path from root to child for nested categories and subcategories. Let’s say you have a table in database in such a way it contains unlimited level of categories and you have another column in the same table which should contain the entire path for the nested categories. So you need to update the column that should contain the entire path for nested category and sub-category using Stored Procedure.

Prerequisites

MySQL 8.0.17

MySQL Table

Let’s say you have created a table called cat under roytuts database in MySQL server with the following structure.

CREATE TABLE `cat` (
  `cat_id` int unsigned NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `p_id` int DEFAULT '0',
  `cat_path` tinytext,
  PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Sample Data

I will store some sample data so that I can test the code right away.

insert  into `cat`(`cat_id`,`cat_name`,`p_id`,`cat_path`)
values (1,'Java',0,NULL),
    (2,'PHP',0,NULL),
    (3,'Frameworks',0,NULL),
    (4,'JSF',3,NULL),
    (5,'Struts',3,NULL),
    (6,'Spring',3,NULL),
    (7,'Hibernate',3,NULL),
    (8,'Webservices',0,NULL),
    (9,'REST',8,NULL),
    (10,'SOAP',8,NULL),
    (11,'Database',0,NULL),
    (12,'MySQL',11,NULL),
    (13,'Oracle',11,NULL),
    (14,'Tutorials',0,NULL),
    (15,'Scripts',14,NULL),
    (16,'JavaScript',15,NULL),
    (17,'JQuery',15,NULL);

Stored Procedure

You need to have null values for all rows in the column cat_path of the cat table.

Now below Stored Procedure will build the nested category path from root to child category and update the column cat_path in the table.

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `nested_path`()
BEGIN
	DECLARE path_count, p_count INT;
    SELECT COUNT(*) INTO p_count FROM cat WHERE p_id = 0;
    UPDATE cat SET cat_path = NULL;
    UPDATE cat a, cat b SET a.cat_path = b.cat_name WHERE b.p_id = 0 AND a.p_id = b.cat_id;
    SELECT COUNT(*) INTO path_count FROM cat WHERE cat_path IS NULL;
    
    WHILE path_count > p_count DO
        UPDATE cat a, cat b SET a.cat_path = CONCAT(b.cat_path, '->', b.cat_name)
        WHERE b.cat_path IS NOT NULL AND a.p_id = b.cat_id;
        SELECT COUNT(*) INTO path_count FROM cat WHERE cat_path IS NULL;
    END WHILE;
END//
DELIMITER ;

Testing the Procedure

Execute the below two statements separately one by one in the same order as written below.

Execute the Stored Procedure.

CALL nested_path;

Now select records from table cat.

Select * from cat;

Output

You will see similar to the below output as shown in the image.

find path in hierarchical categories

Source Code

Download

That’s all. Thanks for reading.

Leave a Comment