Nested Set Model: Inserting Node at the end of SubNodes

Question

Existing Data (name, lft, rgt):

Root, 1, 4
Item1, 2, 3

Looks like:

- Root
--- Item1

How do you insert a new node (Item2) BELOW Item1? My system's current logic follows most examples I've found online but the result is Item2 ABOVE Item1.

- Root
--- Item1
--- Item2

Thank you for the help.

Answer 1

Think of the nested sets model as of an XML file with lft and rgt being the lines where the staring and ending tags reside:

1  <root>
2   <item1>
3   </item1>
4  </root>

To insert a new subtag into the root, you'll need to shift down all subsequent records:

1  <root>
2   <item1>
3   </item1>
4   <item2>
5   </item2>
6  </root>

So you'll need to calculate the item2.lft and item2.rgt (which are the item2.rgt + 1 and item1.rgt + 2, accordingly), and then increment all lft and rgt of all items which are greater than the item1.rgt:

UPDATE  mytable
SET     rgt = rgt + 2
WHERE   rgt > item1.rgt

UPDATE  mytable
SET     lft = lft + 2
WHERE   lft > item1.rgt