嵌套集模型：在子节点末尾插入节点(Nested Set Model: Inserting Node at the end of SubNodes)

嵌套集模型：在子节点末尾插入节点(Nested Set Model: Inserting Node at the end of SubNodes)

现有数据（name，lft，rgt）：

Root, 1, 4 Item1, 2, 3

好像：

- Root --- Item1

如何在项目1下面插入一个新节点（Item2）？ 我的系统当前逻辑遵循我在网上找到的大多数示例，但结果是Item2 ABOVE Item1。

- Root --- Item1 --- Item2

感谢您的帮助。

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.

最满意答案

将嵌套集模型视为XML文件，其中lft和rgt是起始和结束标记所在的行：

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

要在root插入新的子标签，您需要将所有后续记录向下移动：

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

所以你需要计算item2.lft和item2.rgt （相应的是item2.rgt + 1和item1.rgt + 2 ），然后增加所有大于item1.rgt项目的lft和rgt item1.rgt ：

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

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