Nested Set Model: Inserting Node at the end of SubNodes

nested-sets, sql

Solution

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

Problem

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.

Original source