Nested set - can't move node to last parent

Question

I'm currently facing a problem, that I can't solve. I am using a nested set for all my categories, and when I need to move them, there is some kind of bug. My problem is, that if I want to move a node - e.g. ID=2 from "top-level" to underlay ID=1 and change Rgt=1 and Lft=4 on ID=1 + Rgt=2 and Lft=3 on ID=2, then it will all work.

The problem is, that if I try to move the node with ID=2 to the last node - ID=5, Rgt=9, Lft=10, then it really ruins my table - and I can't see what is going wrong in the mySQL query - hope you can help...

My table WHEN I START is like this:

ID | Name        | Rgt  | Lft | ParentID
---+-------------+------+-----+---------
1  | Trousers    | 1    | 2   | 0
---+-------------+------+-----+---------
2  | Jeans       | 3    | 4   | 0
---+-------------+------+-----+---------
3  | Tops        | 5    | 6   | 0
---+-------------+------+-----+---------
4  | T-shirts    | 7    | 8   | 0
---+-------------+------+-----+---------
5  | Shirts      | 9    | 10  | 0
---+-------------+------+-----+---------

And my query is like this:

SELECT
    @node_id := '2',
    @node_pos_left := '3',
    @node_pos_right := '4',
    @parent_id := '5',
    @parent_pos_right := '10';
SELECT
    @node_size := @node_pos_right - @node_pos_left + 1; 



UPDATE ss_C_Categories
SET Lft = 0-(Lft), `Rgt` = 0-(Rgt)
WHERE Lft >= @node_pos_left AND Rgt <= @node_pos_right;



UPDATE ss_C_Categories
SET Lft = Lft - @node_size
WHERE Lft > @node_pos_right;

UPDATE ss_C_Categories
SET Rgt = Rgt - @node_size
WHERE Rgt > @node_pos_right;



UPDATE ss_C_Categories
SET Lft = Lft + @node_size
WHERE Lft >= IF(@parent_pos_right > @node_pos_right, @parent_pos_right - @node_size, @parent_pos_right);

UPDATE ss_C_Categories
SET Rgt = Rgt + @node_size
WHERE Rgt >= IF(@parent_pos_right > @node_pos_right, @parent_pos_right - @node_size, @parent_pos_right);



UPDATE ss_C_Categories
SET
    Lft = 0-(Lft)+IF(@parent_pos_right > @node_pos_right, @parent_pos_right - @node_pos_right - 1, @parent_pos_right - @node_pos_right - 1 + @node_size),
    Rgt = 0-(Rgt)+IF(@parent_pos_right > @node_pos_right, @parent_pos_right - @node_pos_right - 1, @parent_pos_right - @node_pos_right - 1 + @node_size)
WHERE Lft <= 0-@node_pos_left AND Rgt >= 0-@node_pos_right;

UPDATE ss_C_Categories
SET ParentID = @parent_id
WHERE CategoryID = @node_id;

After performing the query, this is what my table looks like:

ID | Name        | Rgt  | Lft | ParentID
---+-------------+------+-----+---------
1  | Trousers    | 1    | 2   | 0
---+-------------+------+-----+---------
2  | Jeans       | 10   | 11  | 5
---+-------------+------+-----+---------
3  | Tops        | 5    | 6   | 0
---+-------------+------+-----+---------
4  | T-shirts    | 3    | 4   | 0
---+-------------+------+-----+---------
5  | Shirts      | 7    | 8   | 0
---+-------------+------+-----+---------

I've used this answer as my inspiration on how to do these moves: https://stackoverflow.com/a/1274175/1308905

Answer 1

Well, now I've made my own MySQL with help from a lot of drawings, and I think this should be the way to go, IF (and only in exactly this case): - You are going to move a node from one parent to another. In other words - DON'T use this for moving to "top-level" (yet). I haven't tested it yet.

Though it should be bulletproof for moving a sub-tree to another parent (no matter what level the new parent is on). :-)

$query = $this->prepare("           
    SELECT
        @node_id := :nodeID, #id of the node, you're moving.
        @node_pos_left := :nodeLeft, #left value of the node, you're moving
        @node_pos_right := :nodeRight, #right value of the node, you're moving
        @parent_id := :parentID, #id of the parent node, you're moving to

        @parent_pos_right := :parentRight; #right value of the parent node, you're moving to.

    SELECT
        @node_size := @node_pos_right - @node_pos_left + 1; 

    SELECT
        @node_id := :nodeID, 
        @node_pos_left := :nodeLeft, 
        @node_pos_right := :nodeRight, 
        @parent_id := :parentID, 

        @parent_pos_right := :parentRight; 
    SELECT
        @node_size := @node_pos_right - @node_pos_left + 1; 


    UPDATE ss_C_Categories
    SET Lft = 0-(Lft), Rgt = 0-(Rgt)
    WHERE Lft >= @node_pos_left AND Rgt <= @node_pos_right;



    UPDATE ss_C_Categories
    SET Lft = Lft + @node_size
    WHERE Lft >= @parent_pos_right;

    UPDATE ss_C_Categories
    SET Rgt = Rgt + @node_size
    WHERE Rgt >= @parent_pos_right;


    UPDATE ss_C_Categories
    SET Lft = 0 - (Lft) + (@parent_pos_right - @node_pos_right + 1)
    WHERE Lft < 0;

    UPDATE ss_C_Categories
    SET Rgt = 0 - (Rgt) + (@parent_pos_right - @node_pos_right + 1)
    WHERE Rgt < 0;



    UPDATE ss_C_Categories
    SET Lft = Lft - @node_size
    WHERE 
        Lft > @node_pos_right;


    UPDATE ss_C_Categories
    SET Rgt = Rgt - @node_size
    WHERE 
        Rgt > @node_pos_right;



    UPDATE
        ".DB_PREFIX."C_Categories
    SET
        CategoryName = :name,
        MetaTag = :metatag,
        ParentID = @parent_id,
        Description = :desc
    WHERE
        CategoryID = @node_id
    ORDER BY
        CategoryID desc
    LIMIT 1;
");


$params = array(
    array('nodeLeft', $nodeLeft, 'INT'),
    array('nodeRight', $nodeRight, 'INT'),
    array('nodeID', $nodeID, 'INT'),
    array('parentRight', $parentRight, 'INT'),
    array('parentID', $parentID, 'INT'),
    array('name', $data['name'], 'STR'),
    array('metatag', $data['metatag'], 'STR'),
    array('desc', $data['description'], 'STR')
);

$this->exec($query, $params);

Again with inspiration from the link in the question.