I have a tree (nested categories) that is stored as follows:
CREATE TABLE `category` ( `category_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `category_name` varchar(100) NOT NULL, `parent_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`category_id`), UNIQUE KEY `category_name_UNIQUE` (`category_name`,`parent_id`), KEY `fk_category_category1` (`parent_id`,`category_id`), CONSTRAINT `fk_category_category1` FOREIGN KEY (`parent_id`) REFERENCES `category` (`category_id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci
I need to pass my client language (PHP) using node information (child + parent) so that it can build a tree in memory. I can customize my PHP code, but I think the operation will be easier if I can just get the strings in that order so that all parents come in front of their children. I could do this if I knew the level for each node:
SELECT category_id, category_name, parent_id FROM category ORDER BY level
Can you think of a way (lookup, stored procedure or something else ...) to calculate the node level? I think everything is fine, if it is not in real time, and I need to recount it when modifying the node.
First update: progress so far
I wrote these triggers based on Amarghosh reviews:
DROP TRIGGER IF EXISTS `category_before_insert`; DELIMITER // CREATE TRIGGER `category_before_insert` BEFORE INSERT ON `category` FOR EACH ROW BEGIN IF NEW.parent_id IS NULL THEN SET @parent_level = 0; ELSE SELECT level INTO @parent_level FROM category WHERE category_id = NEW.parent_id; END IF; SET NEW.level = @parent_level+1; END// DELIMITER ; DROP TRIGGER IF EXISTS `category_before_update`; DELIMITER // CREATE TRIGGER `category_before_update` BEFORE UPDATE ON `category` FOR EACH ROW BEGIN IF NEW.parent_id IS NULL THEN SET @parent_level = 0; ELSE SELECT level INTO @parent_level FROM category WHERE category_id = NEW.parent_id; END IF; SET NEW.level = @parent_level+1; END// DELIMITER ;
It seems to be great for attachments and modifications. But this does not work for deletion: MySQL Server does not start triggers when rows are updated from ON UPDATE CASCADE foreign keys.
The first obvious idea is to write a new trigger for deletion; however, a trigger in the categories table is not allowed to change other rows in the same table:
DROP TRIGGER IF EXISTS `category_after_delete`; DELIMITER // CREATE TRIGGER `category_after_delete` AFTER DELETE ON `category` FOR EACH ROW BEGIN UPDATE category SET parent_id=NULL WHERE parent_id = OLD.category_id; END// DELIMITER ;
Mistake:
Grid editing error: SQL error (1442): It is not possible to update the table 'category' into a stored function / trigger, because it is already used by the expression that this stored function / trigger is called.
Second update: working solution (if not proven)
My first attempt was quite reasonable, but I found a problem that I could not solve: when you start a series of operations from a trigger, MySQL will not allow you to change other rows from the same table. Since node needs to adjust the level of all descendants to delete, I hit the wall.
In the end, I changed the approach using the code here : instead of adjusting individual levels when the node changes, I have code to calculate all levels, and I run it every time I edit. Since it takes a very complex query to compute and collect data slowly, I cache it in a table. In my case, this is an acceptable solution, since publications should be rare.
1. New table for cached levels:
CREATE TABLE `category_level` ( `category_id` int(10) NOT NULL, `parent_id` int(10) DEFAULT NULL,
2. Auxiliary function for calculating levels
If I really understand how this works, it does not return anything useful in itself. Instead, it stores data in session variables.
CREATE FUNCTION `category_connect_by_parent_eq_prior_id`(`value` INT) RETURNS int(10) READS SQL DATA BEGIN DECLARE _id INT; DECLARE _parent INT; DECLARE _next INT; DECLARE CONTINUE HANDLER FOR NOT FOUND SET @category_id = NULL; SET _parent = @category_id; SET _id = -1; IF @category_id IS NULL THEN RETURN NULL; END IF; LOOP SELECT MIN(category_id) INTO @category_id FROM category WHERE COALESCE(parent_id, 0) = _parent AND category_id > _id; IF @category_id IS NOT NULL OR _parent = @start_with THEN SET @level = @level + 1; RETURN @category_id; END IF; SET @level := @level - 1; SELECT category_id, COALESCE(parent_id, 0) INTO _id, _parent FROM category WHERE category_id = _parent; END LOOP; END
3. Procedure for starting the recount process
It basically encapsulates a complex query that retrieves the levels supported by the helper function.
CREATE PROCEDURE `update_category_level`() SQL SECURITY INVOKER BEGIN DELETE FROM category_level; INSERT INTO category_level (category_id, parent_id, level) SELECT hi.category_id, parent_id, level FROM ( SELECT category_connect_by_parent_eq_prior_id(category_id) AS category_id, @level AS level FROM ( SELECT @start_with := 0, @category_id := @start_with, @level := 0 ) vars, category WHERE @category_id IS NOT NULL ) ho JOIN category hi ON hi.category_id = ho.category_id; END
4. Triggers to update the current cache table
CREATE TRIGGER `category_after_insert` AFTER INSERT ON `category` FOR EACH ROW BEGIN call update_category_level(); END CREATE TRIGGER `category_after_update` AFTER UPDATE ON `category` FOR EACH ROW BEGIN call update_category_level(); END CREATE TRIGGER `category_after_delete` AFTER DELETE ON `category` FOR EACH ROW BEGIN call update_category_level(); END
5. Known issues
- This is pretty suboptimal if nodes change frequently.
- MySQL does not allow transactions or table locking in triggers and procedures. You must take care of these details where you edit the nodes.