GetReparentedValue: примеры (SQL)

Базовые примеры использования

Создание таблицы и заполнение данными для примеров:

CREATE TABLE EmployeeHierarchy (
    NodeId hierarchyid PRIMARY KEY,
    EmployeeName nvarchar(50)
);

INSERT INTO EmployeeHierarchy (NodeId, EmployeeName) VALUES
('0/', 'Генеральный директор'),
('0/1/', 'Менеджер отдела А'),
('0/1/1/', 'Сотрудник А1'),
('0/2/', 'Менеджер отдела Б'),
('0/2/1/', 'Сотрудник Б1');

Пример 1: Перемещение поддерева (менеджера отдела А и его подчиненных) под другого родителя (менеджера отдела Б).

DECLARE @old_root hierarchyid = hierarchyid::Parse('/0/1/');
DECLARE @new_root hierarchyid = hierarchyid::Parse('/0/2/');

SELECT 
    NodeId.ToString() AS OldPath,
    EmployeeName,
    NodeId.GetReparentedValue(@old_root, @new_root).ToString() AS NewPath
FROM EmployeeHierarchy
WHERE NodeId.IsDescendantOf(@old_root) = 1;

OldPath    | EmployeeName      | NewPath
---------- | ----------------- | ----------
/0/1/      | Менеджер отдела А | /0/2/
/0/1/1/    | Сотрудник А1      | /0/2/1/

Пример 2: Попытка перемещения узла, который не является потомком указанного старого корня. Функция возвращает исходное значение.

DECLARE @node hierarchyid = hierarchyid::Parse('/0/2/1/');
DECLARE @wrong_old_root hierarchyid = hierarchyid::Parse('/0/1/');
DECLARE @new_root hierarchyid = hierarchyid::Parse('/0/3/');

SELECT @node.GetReparentedValue(@wrong_old_root, @new_root).ToString();

/0/2/1/

Пример 3: Работа с NULL. Если оба аргумента NULL, результат NULL.

DECLARE @node hierarchyid = hierarchyid::Parse('/0/1/');
SELECT CONCAT('''', @node.GetReparentedValue(NULL, NULL), '''');

NULL

Похожие функции в MS SQL

Для работы с типом hierarchyid существует несколько методов:

• GETANCESTOR(n) — Возвращает предка текущего узла на указанном уровне. Полезен для навигации вверх по иерархии.
• GETDESCENDANT(child1, child2) — Возвращает дочерний узел. Используется для вставки новых узлов в определенную позицию между другими узлами.
• GETLEVEL() — Возвращает целое число, представляющее глубину узла в иерархии (уровень).
• GetRoot() — Статический метод, возвращающий корень иерархии.
• ISDESCENDANTOF(parent) — Определяет, является ли узел потомком указанного узла. Часто используется в условиях WHERE.

GetReparentedValue предпочтительнее для массового перемещения целых ветвей дерева. Для навигации и проверок больше подходят GETANCESTOR и ISDESCENDANTOF.

Альтернативы в других СУБД и языках

Концепция иерархических данных реализована по-разному в различных СУБД. Прямого аналога GetReparentedValue часто нет.

PostgreSQL (с использованием ltree): Модуль ltree предлагает метки пути. Для изменения пути можно использовать строковые функции.

-- Пример: замена части пути
SELECT path, replace(path::text, 'A.B', 'X.Y')::ltree AS new_path
FROM categories WHERE path ~ '*.A.B.*';

path    | new_path
------- | ----------
A.B.C   | X.Y.C

Oracle (с использованием CONNECT BY или рекурсивных подзапросов): Перемещение достигается обновлением поля parent_id.

-- Пример обновления родителя для узла и его потомков
UPDATE employee
SET parent_id = 200
WHERE id IN (SELECT id FROM employee START WITH id = 100 CONNECT BY PRIOR id = parent_id);

MySQL, SQLite, Sybase: Отсутствует встроенный иерархический тип, аналогичный hierarchyid. Структура обычно реализуется через таблицу с полями id и parent_id (Adjacency List). Для перемещения поддерева требуется рекурсивное обновление или использование вложенных множеств (Nested Sets), где обновление более сложное. Алгоритм заключается в пересчете левых/правых ключей для всей ветки.

Типичные ошибки

Ошибка 1: Использование несовместимого типа данных в качестве аргумента.

DECLARE @node hierarchyid = hierarchyid::Parse('/1/');
SELECT @node.GetReparentedValue('/0/', '/2/').ToString(); -- Аргументы не преобразованы в hierarchyid

Msg 206, Level 16, State 2: Operand type clash: varchar is incompatible with hierarchyid

Ошибка 2: Перемещение узла «в себя» или создание циклической зависимости при некорректном обновлении таблицы.

DECLARE @node hierarchyid = hierarchyid::Parse('/0/1/');
DECLARE @child hierarchyid = hierarchyid::Parse('/0/1/1/');

-- Попытка переместить родителя в своего же потомка
UPDATE EmployeeHierarchy
SET NodeId = NodeId.GetReparentedValue(@node, @child)
WHERE NodeId = @node;

Такой запрос выполнится, но сделает узел '/0/1/' потомком '/0/1/1/', что нарушит логику дерева. Следующие запросы к иерархии могут завершиться ошибкой.

Ошибка 3: Ожидание, что функция автоматически обновит данные в таблице. Функция только вычисляет новое значение. Для сохранения изменений необходим оператор UPDATE.

История изменений

Функция GetReparentedValue была введена вместе с типом данных hierarchyid в Microsoft SQL Server 2008. С момента своего появления в синтаксисе функции или ее поведении значительных изменений не происходило. Все последующие версии SQL Server (включая 2012, 2014, 2016, 2017, 2019, 2022) поддерживают функцию без обратных несовместимых изменений.

Основные улучшения, связанные с иерархическими данными, касались производительности индексирования и оптимизации запросов, но не затрагивали конкретно эту функцию.

Расширенные примеры использования

Пример 1: Пакетное перемещение всех потомков одного корня под другой корень с немедленным обновлением таблицы.

DECLARE @OldRoot hierarchyid = (SELECT NodeId FROM EmployeeHierarchy WHERE EmployeeName = 'Менеджер отдела А');
DECLARE @NewRoot hierarchyid = (SELECT NodeId FROM EmployeeHierarchy WHERE EmployeeName = 'Менеджер отдела Б');

BEGIN TRANSACTION;
    UPDATE EmployeeHierarchy
    SET NodeId = NodeId.GetReparentedValue(@OldRoot, @NewRoot)
    WHERE NodeId.IsDescendantOf(@OldRoot) = 1;
COMMIT TRANSACTION;

SELECT NodeId.ToString() AS Path, EmployeeName FROM EmployeeHierarchy ORDER BY NodeId;

Path     | EmployeeName
-------  | -----------------
/0/      | Генеральный директор
/0/2/    | Менеджер отдела Б
/0/2/1/  | Сотрудник Б1
/0/2/2/  | Менеджер отдела А  -- Ветка перемещена
/0/2/2/1/| Сотрудник А1

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

ALTER TABLE EmployeeHierarchy
ADD AlternatePath AS (NodeId.GetReparentedValue(hierarchyid::Parse('/0/'), hierarchyid::Parse('/1/')));

SELECT EmployeeName, NodeId.ToString() AS PrimaryPath, AlternatePath.ToString() AS BackupPath
FROM EmployeeHierarchy;

Пример 3: Сценарий объединения двух деревьев. Перемещение всего поддерева из одной иерархии (с корнем /A/) в другую (с корнем /B/), когда структура хранится в одной таблице.

-- Предположим, есть узлы с корнем /A/
INSERT INTO EmployeeHierarchy VALUES ('/A/', 'Другой директор'), ('/A/1/', 'Чужой менеджер');

DECLARE @OldTreeRoot hierarchyid = hierarchyid::Parse('/A/');
DECLARE @NewTreeRoot hierarchyid = hierarchyid::Parse('/0/3/'); -- Новый узел в основном дереве

UPDATE EmployeeHierarchy
SET NodeId = NodeId.GetReparentedValue(@OldTreeRoot, @NewTreeRoot)
WHERE NodeId.IsDescendantOf(@OldTreeRoot) = 1;

Пример 4: Построение «пути для отображения» через объединение с самой таблицей, используя вычисленный reparented-путь.

-- Показ, как выглядела бы иерархия, если бы ветка была перемещена (без фактического UPDATE)
SELECT 
    src.EmployeeName,
    src.NodeId.ToString() AS CurrentPath,
    src.NodeId.GetReparentedValue(hierarchyid::Parse('/0/1/'), hierarchyid::Parse('/0/3/')).ToString() AS HypotheticalPath,
    COALESCE(par.EmployeeName, 'Корень') AS [Current Parent]
FROM EmployeeHierarchy src
LEFT JOIN EmployeeHierarchy par ON src.NodeId.GetAncestor(1) = par.NodeId
WHERE src.NodeId.IsDescendantOf(hierarchyid::Parse('/0/1/')) = 1;