hierarchy-data-closure-table
hierarchy-data-closure-table copied to clipboard
developerworks
Moving nodes not working anymore
~My bad.~
Run mysql/all-in-one.sql and then try moving C0 under B0 or any A node. Instead of having 1,3,2 as breadcrumbs, it will have 1,2,3.
What's interesting is that I see now I had the same issue 4 years ago #4 and somehow I came to conclusion that it was procedure showing the tree that was problematic. ~@developerworks Can you confirm this?~
After couple of hours of playing with it, the problem is that procedure that moves the nodes will fail to delete parent associations when you set parent to null (create another root element)
Here is the fixed version of p_get_tree that returns the proper breadcrumbs:
Notice, that i added "GROUP_CONCAT(
crumbs.ancestor_id ORDER BY path_length DESC SEPARATOR ','
) AS breadcrumbs"
Without the ORDER BY the breadcrumbs lose proper ordering, you can see more about this in this article, read the comments there: https://www.percona.com/blog/2011/02/14/moving-subtrees-in-closure-table/
` DELIMITER $$
USE hierarchy_data$$
DROP PROCEDURE IF EXISTS p_get_tree$$
CREATE PROCEDURE p_get_tree(
node_id INT UNSIGNED
) COMMENT 'Query all descendants nodes by a node id, return as a result set'
BEGIN
SELECT
node.id,
node.is_deleted,
node.parent_id,
CONCAT(
REPEAT('-', path.path_length),
node.name
) AS name,
path.path_length,
GROUP_CONCAT(
crumbs.ancestor_id ORDER BY path_length DESC SEPARATOR ','
) AS breadcrumbs
FROM
prefix_nodes AS node
JOIN prefix_nodes_paths AS path
ON node.id = path.descendant_id
JOIN prefix_nodes_paths AS crumbs
ON crumbs.descendant_id = path.descendant_id
WHERE path.ancestor_id = node_id
AND node.is_deleted = 0
GROUP BY node.id
ORDER BY breadcrumbs ;
END$$
DELIMITER ;
DELIMITER $$`
