mysql - tipos - Mover nodo en conjunto anidado
select*menos un campo mysql (11)
Necesitaría una consulta MySQL que mueva un nodo y todos sus elementos secundarios dentro de un conjunto anidado. Encontré este sitio, pero esa función parece tan ilógica: no hay un conjunto de datos treeid o treeid en un modelo de conjunto anidado, y el código en sí mismo es más largo que lo que se necesita. La única columna adicional que tengo en la tabla es parent .
No podría simplemente eliminar y agregar el nodo nuevamente ya que perderá su ID.
Mover subárboles es muy costoso y complejo en el diseño de conjuntos anidados.
Debe considerar un diseño diferente para representar árboles.
Por ejemplo, si usa el diseño de Enumeración de ruta, almacena la lista de ancestros directos de cada nodo como una cadena concatenada.
id path
1 1/
2 1/2/
3 1/3/
4 1/3/4/
5 1/3/5/
Luego mueve un subárbol (digamos que el nodo 3 se mueve para ser un hijo del nodo 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, ''%'');
Vea el artículo en mi blog para almacenar y usar datos jerárquicos en MySQL :
Para mover una rama completa en una tabla de este tipo, solo tendrá que actualizar el parent la raíz (una sola fila)
Necesitarás crear una función:
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
y usarlo en una consulta:
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
Tengo un procedimiento almacenado que mueve un nodo en un conjunto anidado a un nuevo nodo padre. Estoy usando una tabla llamada "categoría" en una base de datos MySQL / InnoDB llamada "somedb". Por supuesto, si el destino es una subcategoría de la categoría que desea mover, este procedimiento arruinará las cosas, por lo que debe asegurarse de que no intente incrustar un nodo dentro de sí mismo. Lo dejaré como ejercicio al lector para que este procedimiento sea seguro para ese caso.
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
Creo que con dos columnas adicionales para almacenar los valores originales derecho e izquierdo del Nodo (y todos los subnodos posteriores) el algoritmo puede simplificarse. He trabajado los ejemplos con lápiz y papel, así que si encuentras algún agujero en el algoritmo, házmelo saber.
El nodo objetivo (el nuevo padre del nodo que se está moviendo) es tNode. El valor izquierdo del nodo objetivo es tNode.L y el valor correcto es tNode.R. De forma similar, el nodo que está moviendo es mNode y los valores izquierdo y derecho para mNode son mNode.L y mNode.R. Las dos columnas adicionales son mNode.SL y mNode.SR
Así que, en general, tenemos 4 columnas para la manipulación R, L, SL y SR
Paso 1
calcular
delta1 = (mNode.R - mNode.L) + 1
Paso 2
Guarde el mNode original L y R en columnas SL y 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 ;
Paso 3
Do For all Nodes
IF L > mNode.SR
L = L + delta1
IF R > mNode.SR
R = R + delta1
Ahora el mNode se separa de Árbol y el Árbol se ajusta sin mNode.
Etapa 4
calcular
delta2 = (tNode.R - mNode.SL)
Step5
Do for all Nodes
IF L >= tNode.R
L = L + delta1
IF R >= tNode.R
R = R + delta1
Ahora hemos ajustado el árbol (y el nodo de destino) para aceptar la cantidad de nodos que se eliminaron.
Step6
Adjunte mNode en tNode y restablezca los valores de la columna 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 ;
Después de todos estos pasos deberíamos haber movido mNode debajo del tNode.
$ row es una matriz que representa la fila que tengo que mover; debe ser así:
Array ( [lft] => 5 [rgt] => 10 [width] => 6 )
$ row2 es una matriz que representa el nodo de destino;
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"]);
Sé que esta es una vieja pregunta, pero acabo de usar la respuesta, pero para SQL Server. Si alguien lo quiere, aquí está el código para un SQL Server Stored Proc basado en la respuesta aceptada.
CREATE PROCEDURE [dbo].[Item_Move]
@id uniqueidentifier,
@destinationId uniqueidentifier
AS
BEGIN
SET NOCOUNT ON;
declare @moverLeft int,
@moverRight int,
@destinationRight int,
@node_size int
-- step 0: Initialize parameters.
SELECT
@moverLeft = leftExtent,
@moverRight = rightExtent
FROM
Item
WHERE
id = @id
SELECT
@destinationRight = rightExtent
FROM
Item
WHERE
id = @destinationId
SELECT
@node_size = @moverRight - @moverLeft + 1; -- ''size'' of moving node (including all it''s sub nodes)
-- step 1: temporary "remove" moving node
UPDATE Item
SET leftExtent = 0-(leftExtent), rightExtent = 0-(rightExtent), updatedDate = GETDATE()
WHERE leftExtent >= @moverLeft AND rightExtent <= @moverRight;
-- step 2: decrease left and/or right position values of currently ''lower'' items (and parents)
UPDATE Item
SET leftExtent = leftExtent - @node_size, updatedDate = GETDATE()
WHERE leftExtent > @moverRight;
UPDATE Item
SET rightExtent = rightExtent - @node_size, updatedDate = GETDATE()
WHERE rightExtent > @moverRight;
-- step 3: increase left and/or right position values of future ''lower'' items (and parents)
UPDATE Item
SET leftExtent = leftExtent + @node_size, updatedDate = GETDATE()
WHERE leftExtent >= CASE WHEN @destinationRight > @moverRight THEN @destinationRight - @node_size ELSE @destinationRight END;
UPDATE Item
SET rightExtent = rightExtent + @node_size, updatedDate = GETDATE()
WHERE rightExtent >= CASE WHEN @destinationRight > @moverRight THEN @destinationRight - @node_size ELSE @destinationRight END;
-- step 4: move node (and it''s subnodes) and update it''s parent item id
UPDATE Item
SET
leftExtent = 0-(leftExtent) + CASE WHEN @destinationRight > @moverRight THEN @destinationRight - @moverRight - 1 ELSE @destinationRight - @moverRight - 1 + @node_size END,
rightExtent = 0-(rightExtent) + CASE WHEN @destinationRight > @moverRight THEN @destinationRight - @moverRight - 1 ELSE @destinationRight - @moverRight - 1 + @node_size END,
updatedDate = GETDATE()
WHERE leftExtent <= 0-@moverLeft AND rightExtent >= 0-@moverRight;
UPDATE Item
SET parentId = @destinationId, updatedDate = GETDATE()
WHERE id = @id;
END
Es bastante simple, primero defina un procedimiento almacenado:
CREATE DEFINER=`root`@`localhost` PROCEDURE `move_item`(
IN itemId BIGINT, IN kind SMALLINT,
IN newSiblingId BIGINT UNSIGNED, IN newSiblingKind SMALLINT, IN newParentId BIGINT UNSIGNED,
IN jobId BIGINT UNSIGNED, IN companyId BIGINT UNSIGNED,
OUT outSucess SMALLINT UNSIGNED)
proc_label:BEGIN
a continuación, necesitamos algunas variables locales:
DECLARE oldLeft, oldRight, newLeft, newRight, itemWidth, moveBy INT UNSIGNED DEFAULT 0;
set outSucess =0;
Ahora toma nuestra vieja izquierda y derecha y obtén el ancho
SELECT `LFT`, `RGT` into oldLeft, oldRight from `nodes` where `ID`=itemId LIMIT 1;
SET itemWidth = oldRight - oldLeft + 1;
Ahora tómalos "fuera del árbol" multiplicando por -1
UPDATE `nodes` SET `RGT`=`RGT`* -1, `LFT`=`LFT`* -1 WHERE ``LFT` BETWEEN oldLeft and oldRight;
La siguiente parte no es necesaria ya que el árbol funcionará sin ella, pero está ordenada; cerrar la vieja brecha:
-- Update right
UPDATE `nodes` SET `RGT` = `RGT` - itemWidth WHERE `RGT` > oldRight;
-- Update left
UPDATE `nodes` SET `LFT` = `LFT` - itemWidth WHERE `LFT` > oldRight;
Ahora encuentra la nueva ubicación:
SELECT (`RGT`+1) into newLeft from `nodes` where `ID`=newSiblingId LIMIT 1;
-- No sibling, so make it last in parent
IF (newLeft = 0) AND (newParentId != 0) THEN
SELECT `RGT` into newLeft from `nodes` WHERE `ID`=newParentId LIMIT 1;
END IF;
-- If no previous sibling or parent, set to first item in tree
IF (newLeft=0) OR (newLeft=NULL) THEN SET newLeft=1;
END IF;
Ahora haz un poco de espacio:
-- Update right
UPDATE `nodes` SET `RGT` = `RGT` + itemWidth WHERE `RGT` >= newLeft;
-- Update left
UPDATE `nodes` SET `LFT` = `LFT` + itemWidth WHERE `LFT` >= newLeft;
Finalmente, mueva los nodos que se desplazaron hacia afuera del árbol por * -1, y mientras lo hace, muévalos también a la ubicación correcta:
SET moveBy = OldLeft - NewLeft;
UPDATE `nodes` SET `RGT`=(`RGT`* -1)-moveBy, `LFT`=(`LFT`* -1)-moveBy WHERE `LFT` < 0;
set outSucess =1;
No probado, pegado y ajustado y simplificado de una rutina de trabajo.
Sé que esta publicación es antigua, pero estoy publicando esta solución para todos los demás que llegarán aquí para ver una solución. Encontré esto @ sedna-soft.de. Probé la identificación y funciono perfectamente
-- moves a subtree before the specified position
-- if the position is the rgt of a node, the subtree will be its last child
-- if the position is the lft of a node, the subtree will be inserted before
-- @param l the lft of the subtree to move
-- @param r the rgt of the subtree to move
-- @param p the position to move the subtree before
SET @r: , @l: , @p:
update tree
set
lft = lft + if (@p > @r,
if (@r < lft and lft < @p,
@l - @r - 1,
if (@l <= lft and lft < @r,
@p - @r - 1,
0
)
),
if (@p <= lft and lft < @l,
@r - @l + 1,
if (@l <= lft and lft < @r,
@p - @l,
0
)
)
),
rgt = rgt + if (@p > @r,
if (@r < rgt and rgt < @p,
@l - @r - 1,
if (@l < rgt and rgt <= @r,
@p - @r - 1,
0
)
),
if (@p <= rgt and rgt < @l,
@r - @l + 1,
if (@l < rgt and rgt <= @r,
@p - @l,
0
)
)
)
where @r < @p or @p < @l;
Aquí hay una solución que le permite mover un nodo a cualquier posición en el árbol, ya sea como un hermano o un niño con un solo parámetro de entrada: la nueva posición a la izquierda (newlpos) del nodo.
Fundamentalmente hay tres pasos:
- Crea un nuevo espacio para el subárbol.
- Mueva el subárbol a este espacio.
- Elimina el antiguo espacio desocupado por el subárbol.
En psuedo-sql, se ve así:
//
* -- create new space for subtree
* UPDATE tags SET lpos = lpos + :width WHERE lpos >= :newlpos
* UPDATE tags SET rpos = rpos + :width WHERE rpos >= :newlpos
*
* -- move subtree into new space
* UPDATE tags SET lpos = lpos + :distance, rpos = rpos + :distance
* WHERE lpos >= :tmppos AND rpos < :tmppos + :width
*
* -- remove old space vacated by subtree
* UPDATE tags SET lpos = lpos - :width WHERE lpos > :oldrpos
* UPDATE tags SET rpos = rpos - :width WHERE rpos > :oldrpos
*/
La variable de distancia es la distancia entre las posiciones nueva y antigua, el ancho es el tamaño del subárbol y: tmppos se utiliza para realizar un seguimiento del subárbol que se mueve durante las actualizaciones. Estas variables se definen como:
// calculate position adjustment variables
int width = node.getRpos() - node.getLpos() + 1;
int distance = newlpos - node.getLpos();
int tmppos = node.getLpos();
// backwards movement must account for new space
if (distance < 0) {
distance -= width;
tmppos += width;
}
Para un ejemplo completo de código, vea mi blog en
http://www.ninthavenue.com.au/how-to-move-a-node-in-nested-sets-with-sql
Si te gusta esta solución, por favor vote.
Veo, que este tema es bastante viejo, pero de todos modos sigue sin respuesta. Llegué aquí de Google y no encontré una respuesta directa a esta pregunta.
Entonces, después de investigar un poco encontré una solución bastante fácil.
Todo, lo que necesitaremos para mover nuestro nodo es: posiciones del nodo izquierdo y derecho, posición del nodo padre nuevo correcto. El nodo a la nueva posición se puede mover en cuatro sencillos pasos:
- Cambia las posiciones del nodo y todos sus subnodos en valores negativos, que son iguales a los actuales por módulo.
- Mueva todas las posiciones "arriba", que son más, que pos_right del nodo actual.
- Mueva todas las posiciones "hacia abajo", que son más, que pos_right del nuevo nodo padre.
- Cambie las posiciones del nodo actual y todos sus subnodos, de modo que ahora esté exactamente "después" (o "abajo") del nuevo nodo padre.
Esa es la teoría, ahora - esta realización del algoritmo en MySQL (ejemplo usando 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;
Tenga cuidado: aún puede haber algunos errores de sintaxis en el código SQL, porque realmente uso este algoritmo en PHP de esta manera:
$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);
}
Tenga en cuenta también que ese código puede estar optimizado, pero voy a dejarlo así para una mejor legibilidad. Considere también el bloqueo de tablas si está utilizando conjuntos anidados en sistemas multiusuario.
Espero que mi mensaje ayude a cualquiera, que buscará una solución después de mí. Cualquier comentario y corrección también son bienvenidos.
Gracias por la idea de transformar el lft y rgt a sus contrapartes negativas. Publiqué un enfoque más general para esto aquí: Mover nodo en el árbol Conjuntos anidados .
La función queryBatch () encierra la consulta en una transacción.