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

Краткие примеры использования

Пример создания таблицы с иерархией и проверки отношений между узлами.

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

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

-- Является ли '/1/1/' потомком корня '/'?
SELECT NodeId.ToString() AS NodePath, EmployeeName,
       NodeId.IsDescendantOf(hierarchyid::GetRoot()) AS IsDescendantOfRoot
FROM EmployeeHierarchy
WHERE NodeId = '/1/1/';

NodePath   EmployeeName   IsDescendantOfRoot
/1/1/      Специалист А1   1

-- Является ли '/1/' потомком '/2/'?
SELECT NodeId.ToString() AS NodePath,
       NodeId.IsDescendantOf('/2/') AS IsDescendantOf_2
FROM EmployeeHierarchy
WHERE NodeId = '/1/';

NodePath   IsDescendantOf_2
/1/        0

-- Проверка с NULL значением
DECLARE @NullNode hierarchyid = NULL;
SELECT hierarchyid::Parse('/1/').IsDescendantOf(@NullNode) AS ResultWithNull;

ResultWithNull
NULL

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

Для работы с иерархическими данными в MS SQL также существуют другие методы типа hierarchyid:

• GETANCESTOR(n) — возвращает hierarchyid предка на указанном уровне n выше текущего узла. Полезен для перемещения вверх по иерархии.
• GETDESCENDANT(child1, child2) — возвращает дочерний узел относительно текущего, позволяя вставлять новые узлы в определенную позицию.
• GETLEVEL() — возвращает целое число, представляющее глубину узла в иерархии (уровень).
• GetRoot() — статический метод, возвращающий корневой узел hierarchyid.

Для сравнения, функция IsDescendantOf более специализирована для проверки отношений. В сценариях, где требуется найти всех предков или потомков, часто используется рекурсивный запрос с Common Table Expressions (CTE), особенно если данные не используют тип hierarchyid.

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

Концепция проверки иерархических отношений существует во многих СУБД, но реализация отличается.

PostgreSQL (с модулем ltree): Использует оператор @> для проверки, является ли путь потомком.

-- Пример с ltree
SELECT 'A.B'::ltree @> 'A.B.C'::ltree AS is_descendant;

is_descendant
true

Oracle: Использует оператор CONNECT BY PRIOR или рекурсивные подзапросы (WITH) для навигации по иерархии. Прямого аналога IsDescendantOf нет, но проверка осуществляется через запрос.

-- Проверка, является ли сотрудник потомком своего менеджера
SELECT employee_id
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
AND employee_id = 110; -- Если вернет строку, то 110 является потомком 100.

MySQL: Не имеет встроенного типа для иерархии. Обычно используют рекурсивные запросы (с версии 8.0) или таблицы с полями parent_id.

WITH RECURSIVE cte AS (
    SELECT id FROM categories WHERE id = 1 -- предок
    UNION ALL
    SELECT c.id FROM categories c
    INNER JOIN cte ON c.parent_id = cte.id
)
SELECT COUNT(*) > 0 AS is_descendant FROM cte WHERE id = 5;

SQLite: Также полагается на рекурсивные запросы (WITH RECURSIVE) для работы с иерархией.

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

1. Некорректный формат строки при преобразовании в hierarchyid: Использование строки, не соответствующей формату пути hierarchyid.

-- Попытка использовать неверный путь
SELECT hierarchyid::Parse('invalid_path').IsDescendantOf('/');

Сообщение об ошибке: Msg 6522, Level 16, State 1: Неправильный входной формат для hierarchyid.

2. Сравнение узлов из разных иерархий: Логически функция будет работать, но может привести к неочевидным результатам, если иерархии не связаны.

-- Узлы из логически разных деревьев
SELECT hierarchyid::Parse('/100/').IsDescendantOf('/2/') AS Result;

Result
0

3. Ожидание, что узел считается потомком самого себя: Функция возвращает 0 для одинаковых узлов.

SELECT hierarchyid::Parse('/1/').IsDescendantOf('/1/') AS SelfCheck;

SelfCheck
0

4. Использование с типом данных, отличным от hierarchyid: Попытка вызвать метод для значения другого типа.

DECLARE @WrongType int = 1;
SELECT @WrongType.IsDescendantOf('/');

Сообщение об ошибке: Сообщение об ошибке: 'int' не содержит определения для 'IsDescendantOf'.

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

Тип данных hierarchyid и его метод IsDescendantOf были введены в Microsoft SQL Server 2008. С тех пор в самой функции существенных изменений не было. Однако, улучшения производительности и оптимизации запросов, затрагивающих hierarchyid, происходили в различных версиях SQL Server. Например, в SQL Server 2012 и более поздних версиях были улучшены индексы и работа с иерархическими данными. Важно использовать актуальную версию SQL Server для получения лучшей производительности при работе с большими иерархиями.

Расширенные примеры

1. Поиск всех потомков конкретного узла с использованием индекса.

DECLARE @ManagerNode hierarchyid = '/1/';
SELECT NodeId.ToString() AS NodePath, EmployeeName
FROM EmployeeHierarchy
WHERE NodeId.IsDescendantOf(@ManagerNode) = 1
AND NodeId != @ManagerNode -- исключить сам узел-предок
ORDER BY NodeId;

NodePath   EmployeeName
/1/1/      Специалист А1
/1/2/      Специалист А2

2. Динамическое определение уровня вложенности и проверка принадлежности к определенной ветви.

SELECT
    emp.NodeId.ToString() as EmpNode,
    emp.EmployeeName,
    emp.NodeId.GetLevel() as EmpLevel,
    CASE
        WHEN emp.NodeId.IsDescendantOf('/1/') = 1 THEN 'Принадлежит отделу А'
        WHEN emp.NodeId.IsDescendantOf('/2/') = 1 THEN 'Принадлежит отделу Б'
        ELSE 'Другое подразделение'
    END as DepartmentBranch
FROM EmployeeHierarchy emp
ORDER BY emp.NodeId;

EmpNode   EmployeeName           EmpLevel   DepartmentBranch
/         Генеральный директор   0          Другое подразделение
/1/       Менеджер отдела А     1          Принадлежит отделу А
/1/1/     Специалист А1         2          Принадлежит отделу А
/1/2/     Специалист А2         2          Принадлежит отделу А
/2/       Менеджер отдела Б     1          Принадлежит отделу Б

3. Использование в сочетании с другими методами hierarchyid для вставки нового узла-потомка.

DECLARE @ParentNode hierarchyid = '/1/';
DECLARE @NewChild hierarchyid = @ParentNode.GetDescendant('/1/2/', NULL);
-- Вставка нового сотрудника как последнего ребенка узла '/1/'
INSERT INTO EmployeeHierarchy (NodeId, EmployeeName)
VALUES (@NewChild, 'Новый специалист А3');

-- Проверка, что новый узел действительно потомок
SELECT @NewChild.ToString() AS NewNode,
       @NewChild.IsDescendantOf(@ParentNode) AS CheckResult;

NewNode    CheckResult
/1/3/      1

4. Применение в триггере для обеспечения целостности иерархии (запрет на перенос узла в его же потомка).

CREATE TRIGGER trg_PreventCircularHierarchy
ON EmployeeHierarchy
AFTER UPDATE AS
BEGIN
    IF UPDATE(NodeId)
    BEGIN
        IF EXISTS (
            SELECT 1
            FROM inserted i
            INNER JOIN deleted d ON i.NodeId = d.NodeId -- предположим, что есть ключ
            WHERE i.NodeId.IsDescendantOf(d.NodeId) = 1
            AND i.NodeId != d.NodeId
        )
        BEGIN
            THROW 50000, 'Невозможно переместить узел в его собственного потомка.', 1;
            ROLLBACK TRANSACTION;
        END
    END
END;