Path For Nested Categories
This tutorial shows how to find the entire or full path from root to child for nested categories and subcategories. Let’s say you have a table in database server in such a way that it contains an 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 – 8.0.26
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 COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
`cat_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`p_id` int COLLATE utf8mb4_unicode_ci DEFAULT '0',
`cat_path` tinytext COLLATE utf8mb4_unicode_ci,
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.