Переместите узел во вложенный набор

См. Статью в моем блоге о хранении и использовании иерархических данных в MySQL :

• Иерархические запросы в MySQL

Чтобы переместить целую ветвь в такой таблице , вам просто нужно обновить родительского элемента корня (одна строка)

. Вам нужно будет создать функцию:

CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _id INT;
        DECLARE _parent INT;
        DECLARE _next INT;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

        SET _parent = @id;
        SET _id = -1;

        IF @id IS NULL THEN
                RETURN NULL;
        END IF;

        LOOP
                SELECT  MIN(id)
                INTO    @id
                FROM    t_hierarchy
                WHERE   parent = _parent
                        AND id > _id;
                IF @id IS NOT NULL OR _parent = @start_with THEN
                        SET @level = @level + 1;
                        RETURN @id;
                END IF;
                SET @level := @level - 1;
                SELECT  id, parent
                INTO    _id, _parent
                FROM    t_hierarchy
                WHERE   id = _parent;
        END LOOP;
END

и использовать ее в запросе:

SELECT  CONCAT(REPEAT('    ', level - 1), CAST(hi.id AS CHAR)) AS treeitem, parent, level
FROM    (
        SELECT  hierarchy_connect_by_parent_eq_prior_id(id) AS id, @level AS level
        FROM    (
                SELECT  @start_with := 0,
                        @id := @start_with,
                        @level := 0
                ) vars, t_hierarchy
        WHERE   @id IS NOT NULL
        ) ho
JOIN    t_hierarchy hi
ON      hi.id = ho.id

Перемещение поддеревьев очень дорого и сложно при проектировании вложенных наборов.

Вам следует подумать о другом дизайне для представления деревьев.

Например, если вы используете схему «Перечисление путей», вы сохраняете список прямых предков каждого узла в виде конкатенированной строки.

id path
 1  1/
 2  1/2/
 3  1/3/
 4  1/3/4/
 5  1/3/5/

Затем перемещаете поддерево (скажем, узел 3 перемещается, чтобы стать потомком узла 2):

UPDATE Tree t
 JOIN Tree node2 ON (node2.id = 2)
 JOIN Tree node3 ON (node3.id = 3)
SET t.path = CONCAT(node2.path, REPLACE(t.path, node3.path, node2.path))
WHERE t.path LIKE CONCAT(node3.path, '%');

У меня есть хранимая процедура, которая перемещает узел во вложенном наборе на новый родительский узел. Я использую таблицу под названием «категория» в базе данных MySQL / InnoDB под названием «somedb». Конечно, если место назначения является подкатегорией категории, которую вы хотите переместить, эта процедура все испортит, поэтому убедитесь, что вы не пытаетесь встроить узел внутрь себя. Я оставлю это в качестве упражнения читателю, чтобы сделать эту процедуру безопасной для этого случая.

CREATE PROCEDURE `somedb`.`moveCatParent` (IN cat_a VARCHAR(45), IN cat_b VARCHAR(45))
BEGIN
    START TRANSACTION;

    /* cat_b.lft + 1 is the destination. */
    SELECT @destination := (lft + 1)
    FROM category
    WHERE name = cat_b;

    SELECT @cat_a_width := ((rgt - lft) + 1)
    FROM category
    WHERE name = cat_a;

    /* Rip this table a new cat_a sized hole inside cat_b. */  
    UPDATE category SET rgt = rgt + @cat_a_width WHERE rgt >= @destination;
    UPDATE category SET lft = lft + @cat_a_width WHERE lft >= @destination;

    SELECT @cat_a_lft := lft, @cat_a_rgt := rgt
    FROM category
    WHERE name = cat_a;

    SELECT @diff := @destination - @cat_a_lft;

    /* Move cat_a and all inhabitants to new hole */  
    UPDATE category SET rgt = rgt + @diff WHERE rgt BETWEEN @cat_a_lft AND @cat_a_rgt;
    UPDATE category SET lft = lft + @diff WHERE lft BETWEEN @cat_a_lft AND @cat_a_rgt;

    /* Close the gap created when we moved cat_a. */
    UPDATE category SET rgt = rgt - @cat_a_width WHERE rgt >= @cat_a_lft;
    UPDATE category SET lft = lft - @cat_a_width WHERE lft >= @cat_a_lft;

    COMMIT;
END

Я считаю, что с двумя дополнительными столбцами для хранения исходных правых и левых значений узла (и всех последующих подузлов) алгоритм можно упростить. Я работал с примерами карандашом и бумагой, поэтому, если вы обнаружите какие-либо дыры в алгоритме, сообщите мне.

Целевой узел (новый родительский узел узла, который вы перемещаете) - это tNode. Левое значение целевого узла - tNode.L, а правое значение - tNode.R. Точно так же перемещаемый узел - это mNode, а значения для левого и правого mNode - это mNode.L и mNode.R. Два дополнительных столбца - это mNode.SL и mNode.SR

Таким образом, всего у нас есть 4 столбца для управления R, L, SL и SR

Шаг 1

вычисление

delta1 = (mNode.R - mNode.L) + 1 

Шаг 2

Сохранение mNode исходные L и R в столбцы SL и SR

- For All L between mNode.L and mNode.R 
   mNode.SL = mNode.L ; mNode.L = 0 ;
 - For All R between mNode.L and mNode.R 
   mNode.SR = mNode.R ; mNode.R = 0 ;

Шаг 3

Do For all Nodes
IF L > mNode.SR 
   L = L + delta1
IF R > mNode.SR
   R = R + delta1

Теперь mNode отсоединен от Tree, и Tree настраивается без mNode.

Шаг 4

вычисление

delta2 = (tNode.R - mNode.SL)

Шаг 5

Do for all Nodes
  IF L >= tNode.R
    L = L + delta1
  IF R >= tNode.R
    R = R + delta1

Теперь мы настроили дерево (и целевой узел), чтобы принять количество удаленных узлов.

Шаг 6

Присоедините mNode к tNode и сбросьте значения столбцов SL / SR

Do for all Nodes
 IF SL between mNode.SL and mNode.SR
    L = mNode.SL + delta2 ; mNode.SL = 0  ;
 IF SR between mNode.SL and mNode.SR
    R = mNode.SR + delta2 ; mNode.SR = 0 ;

После всех этих шагов мы должны переместить mNode под tNode.

$ row - это массив, представляющий строку, которую мне нужно переместить; это должно быть так:

Array ( [lft] => 5 [rgt] => 10 [width] => 6 ) 

$ row2 - это массив, представляющий узел судьбы;

Array ( [id] => 5 [lft] => 2 [rgt] => 17 [width] => 16 ) 

...

mysql_query("UPDATE entryCategory SET rgt = rgt + %d - %d, lft = lft + %d - %d WHERE rgt <= %d and lft >= %d;",$row2["rgt"],$row["lft"],$row2["rgt"],$row["lft"],$row["rgt"],$row["lft"]);
mysql_query("UPDATE entryCategory SET rgt = rgt + %d WHERE id=%d;",$row["width"],$row2["id"]);
mysql_query("UPDATE entryCategory SET rgt = rgt - %d, lft = lft - %d  WHERE rgt > %d and lft > %d;",$row["width"],$row["width"],$row["rgt"],$row["rgt"]);