Jump to content

Stored Procedure for Updating Nested Hierarchy


CrimpJiggler

Recommended Posts

I'm using a CRUD system which won't automatically update the lft and rght values of my hierarchical tables when I enter a new row. I don't know what the best way to solve this is, all I can think of is adding a stored procedure to the table so that whenever a new row gets added, the procedure will make sure the lft and rght values get shifted. Is this even possible with stored procedures?

Link to comment
Share on other sites

I can't edit my post, I wanna ask a better question. Is there a simple way to make MySQL handle nested hierarchical database tables for you. My table has a parent_id, lft and rght value. CakePHP handles this automatically but does MySQL have built in functionality for that? If not, can anyone point me towards a simple stored procedure to do it? I know what I need to do, just find out whether the parent node is empty or not, if its empty then I expand it by 2, and shift everything to the left and right of it two. If its not empty, then I just shift everything from the start of the parent node to the right. There must be an automatic way to do it though.

Link to comment
Share on other sites

I just made this procedure which will do for now:

DELIMITER $$
CREATE PROCEDURE rearrangeNodes(IN parentId INT)
BEGIN

	DECLARE parentLft INTEGER;
	SET parentLft = (SELECT lft FROM table WHERE category_id = NEW.parentId);

	UPDATE `table` 

	SET lft = CASE WHEN lft > parentLft THEN
		lft + 2
	ELSE
		lft + 0
	END,		
	rght = CASE WHEN rght >= parentLft THEN
		rght + 2
	ELSE
		rght + 0
	END
	WHERE  lft >= newLft;

END$$
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.