Rich Rich - 6 months ago 16
SQL Question

Nested Set Model: Inserting Node at the end of SubNodes

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

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