在嵌套集中移动节点 [英] Move node in nested set

问题描述

我需要一个MySQL查询，该查询可在一个嵌套集中移动一个节点及其所有子节点.我发现此网站，但该功能似乎非常不合逻辑-嵌套集合模型中没有universeid或treeid，并且代码本身仅比需要的更长.我在表中唯一剩下的一列是parent.

I'd need a MySQL query that moves a node and all its children within a nested set. I found this site, but that function just seems so illogical - there's no universeid or treeid in a nested set model, and the code itself is just longer than what feels required. The only extra column I've got in the table is parent.

我不能只删除并再次添加该节点，因为它将丢失其ID.

I couldn't just remove and add the node again since it will loose its ID.

推荐答案

我看到，这个主题已经很老了，但是无论如何仍然没有答案.我是从Google来到这里的，没有找到这个问题的直接答案.

I see, that this topic is quite old, but anyway it's still unanswered. I got here from Google, and found no direct answer to this question.

因此，经过一番研究，我发现了一个非常简单的解决方案.

So, after a little research I found quite easy solution.

一切，我们需要移动节点的是:节点左右位置，新父节点右边位置.然后可以通过四个简单步骤将节点移动到新位置:

Everything, what we gonna need to move our node is: node left and right positions, new parent node right position. The node to the new position then can be moved in four easy steps:

1. 将节点及其所有子节点的位置更改为负值， 按模块等于当前模块.
2. 将所有位置向上"移动，比当前节点的pos_right还要多.
3. 向下"移动所有位置，这些位置更多，即新父节点的pos_right.
4. 更改当前节点及其所有子节点的位置，以使它现在恰好在新父节点的之后"(或向下").

1. Change positions of node and all it's sub nodes into negative values, which are equal to current ones by module.
2. Move all positions "up", which are more, that pos_right of current node.
3. Move all positions "down", which are more, that pos_right of new parent node.
4. Change positions of current node and all it's subnodes, so that it's now will be exactly "after" (or "down") of new parent node.

这是理论，现在-该算法在MySQL中实现(使用PHP的示例):

That's theory, now - this algorithm realization in MySQL (example using PHP):

-- step 0: Initialize parameters.
SELECT
    @node_id := 1, --put there id of moving node 
    @node_pos_left := 0, --put there left position of moving node
    @node_pos_right := 1, --put there right position of moving node
    @parent_id := 2, --put there id of new parent node (there moving node should be moved)

    @parent_pos_right := 4; --put there right position of new parent node (there moving node should be moved)
SELECT
    @node_size := @node_pos_right - @node_pos_left + 1; -- 'size' of moving node (including all it's sub nodes)

-- step 1: temporary "remove" moving node

UPDATE `list_items`
SET `pos_left` = 0-(`pos_left`), `pos_right` = 0-(`pos_right`)
WHERE `pos_left` >= @node_pos_left AND `pos_right` <= @node_pos_right;

-- step 2: decrease left and/or right position values of currently 'lower' items (and parents)

UPDATE `list_items`
SET `pos_left` = `pos_left` - @node_size
WHERE `pos_left` > @node_pos_right;
UPDATE `list_items`
SET `pos_right` = `pos_right` - @node_size
WHERE `pos_right` > @node_pos_right;

-- step 3: increase left and/or right position values of future 'lower' items (and parents)

UPDATE `list_items`
SET `pos_left` = `pos_left` + @node_size
WHERE `pos_left` >= IF(@parent_pos_right > @node_pos_right, @parent_pos_right - @node_size, @parent_pos_right);
UPDATE `list_items`
SET `pos_right` = `pos_right` + @node_size
WHERE `pos_right` >= IF(@parent_pos_right > @node_pos_right, @parent_pos_right - @node_size, @parent_pos_right);

-- step 4: move node (ant it's subnodes) and update it's parent item id

UPDATE `list_items`
SET
    `pos_left` = 0-(`pos_left`)+IF(@parent_pos_right > @node_pos_right, @parent_pos_right - @node_pos_right - 1, @parent_pos_right - @node_pos_right - 1 + @node_size),
    `pos_right` = 0-(`pos_right`)+IF(@parent_pos_right > @node_pos_right, @parent_pos_right - @node_pos_right - 1, @parent_pos_right - @node_pos_right - 1 + @node_size)
WHERE `pos_left` <= 0-@node_pos_left AND `pos_right` >= 0-@node_pos_right;
UPDATE `list_items`
SET `parent_item_id` = @parent_id
WHERE `item_id` = @node_id;

请注意-SQL代码中仍然可能存在一些语法错误，因为我实际上是在PHP中使用这种算法，如下所示:

Please beware - there still may be some syntax errors in SQL code, because I actually use this algorithm in PHP like this:

$iItemId = 1;
$iItemPosLeft = 0;
$iItemPosRight = 1;
$iParentId = 2;
$iParentPosRight = 4;
$iSize = $iPosRight - $iPosLeft + 1;
$sql = array(

    // step 1: temporary "remove" moving node

    'UPDATE `list_items`
    SET `pos_left` = 0-(`pos_left`), `pos_right` = 0-(`pos_right`)
    WHERE `pos_left` >= "'.$iItemPosLeft.'" AND `pos_right` <= "'.$iItemPosRight.'"',

    // step 2: decrease left and/or right position values of currently 'lower' items (and parents)

    'UPDATE `list_items`
    SET `pos_left` = `pos_left` - '.$iSize.'
    WHERE `pos_left` > "'.$iItemPosRight.'"',
    'UPDATE `list_items`
    SET `pos_right` = `pos_right` - '.$iSize.'
    WHERE `pos_right` > "'.$iItemPosRight.'"',

    // step 3: increase left and/or right position values of future 'lower' items (and parents)

    'UPDATE `list_items`
    SET `pos_left` = `pos_left` + '.$iSize.'
    WHERE `pos_left` >= "'.($iParentPosRight > $iItemPosRight ? $iParentPosRight - $iSize : $iParentPosRight).'"',
    'UPDATE `list_items`
    SET `pos_right` = `pos_right` + '.$iSize.'
    WHERE `pos_right` >= "'.($iParentPosRight > $iItemPosRight ? $iParentPosRight - $iSize : $iParentPosRight).'"',

    // step 4: move node (ant it's subnodes) and update it's parent item id

    'UPDATE `list_items`
    SET
        `pos_left` = 0-(`pos_left`)+'.($iParentPosRight > $iItemPosRight ? $iParentPosRight - $iItemPosRight - 1 : $iParentPosRight - $iItemPosRight - 1 + $iSize).',
        `pos_right` = 0-(`pos_right`)+'.($iParentPosRight > $iItemPosRight ? $iParentPosRight - $iItemPosRight - 1 : $iParentPosRight - $iItemPosRight - 1 + $iSize).'
    WHERE `pos_left` <= "'.(0-$iItemPosLeft).'" AND i.`pos_right` >= "'.(0-$iItemPosRight).'"',
    'UPDATE `list_items`
    SET `parent_item_id` = "'.$iParentItemId.'"
    WHERE `item_id`="'.$iItemId.'"'
);

foreach($sql as $sqlQuery){
    mysql_query($sqlQuery);
}

也请注意，代码可能已经过优化，但是为了更好的可读性，我将其保留下来.如果要在多用户系统中使用嵌套集，还请考虑使用表锁定.

Please note also, that code may be optimized, but I going to leave it like that for better readability. Also consider table locking if you are using nested sets in multi-user systems.

希望我的信息对任何人都有帮助，他们将在我之后寻找解决方案.也欢迎任何评论和更正.

Hope that my message will help to anyone, who will search for a solution after me. Any comments and corrections are also welcome.

这篇关于在嵌套集中移动节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！