text: Q:
  
  Nested set - can't move node to last parent
  
  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
  
  A:
  
  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.
  
  