Как визуализировать только потомков, предков и себя узла в иерархии?

Question

В другом вопросе я спросил о визуализации иерархических данных, хранящихся в таблице в базе данных SQL Server. Я нашел способ визуализировать всю иерархию, используя GraphViz, с некоторыми функциями в T-SQL и Powershell.

Я хочу использовать такую ​​визуализацию для отладки приложения, которое использует похожие данные. Визуализация все хорошо для небольшой иерархии примеров. Но в иерархии тысяч это подавляет.

Когда я отлаживаю свое приложение, я обычно смотрю только на небольшой набор узлов, связанных с данным узлом. В настоящее время единственными связанными узлами, которые важны для меня для данного узла, являются потомки и предки, а также сам узел.

Итак, мне нужен способ визуализации только тех узлов в иерархии, которые являются потомками, предками или самим собой данного узла.

Следующие операторы создают пример базы данных и таблицы, как в связанном вопросе.

CREATE DATABASE HierarchyTest;
GO

USE HierarchyTest;
GO

CREATE TABLE NodeHierarchy (
  PK_NodeID INT NOT NULL
    CONSTRAINT PK_NodeHierarchy PRIMARY KEY,
  FK_ParentNodeID INT NULL
    CONSTRAINT FK_NodeHierarchy_NodeHierarchy FOREIGN KEY
      REFERENCES NodeHierarchy(PK_NodeID),
  Name NVARCHAR(255) NOT NULL
);

Следующее утверждение заполняет таблицу измененной версией иерархии стран, городов и мест. Соединенное Королевство теперь является корневым узлом, и в нем есть больше узлов, представляющих известные английские места проведения.

INSERT INTO NodeHierarchy(PK_NodeID, FK_ParentNodeID, Name)
VALUES
  (1, 18, N'Scotland'),
  (2, 1, N'Glasgow'),
  (3, 1, N'Edinburgh'),
  (4, 1, N'St Andrews'),
  (5, 2, N'The Barrowlands'),
  (6, 2, N'The Cathouse'),
  (7, 2, N'Carling Academy'),
  (8, 2, N'SECC'),
  (9, 2, N'King Tut''s Wah-Wah Hut'),
  (10, 3, N'Henry''s Cellar Bar'),
  (11, 3, N'The Bongo Club'),
  (12, 3, N'Sneaky Pete''s'),
  (13, 3, N'The Picture House'),
  (14, 3, N'Potterrow'),
  (15, 4, N'Aikman''s'),
  (16, 4, N'The Union'),
  (17, 4, N'Castle Sands'),
  (18, NULL, N'United Kingdom'),
  (19, 15, N'Upstairs'),
  (20, 15, N'Downstairs'),
  (21, 16, N'Venue 1'),
  (22, 16, N'Venue 2'),
  (23, 18, N'England'),
  (24, 23, N'Manchester'),
  (25, 24, N'Apollo Theatre'),
  (26, 18, N'Liverpool'),
  (27, 26, N'Cavern Club');

Следующее изображение является выводом скрипта Powershell generate-graph.ps1 перечислены в связанном вопросе. Если версия уменьшенного размера Stack Overflow выглядит некрасиво, посмотрите на полноразмерное изображение.

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

В Freemind я нарисовал грубую диаграмму того, что я хотел бы видеть вместо этого:

Как извлечь только те данные, которые имеют отношение к Сент-Эндрюсу, чтобы я мог передать их в GraphViz?

Answer 1

Я не думаю, что вы обращаете внимание на использование объединения, это намного проще:

declare @nodeid int, @parentID int
select @nodeid = PK_NodeID, @parentID = FK_ParentNodeID
    from NodeHierarchy where name = 'St Andrews'

select PK_NodeID, FK_ParentNodeID, Name
    from NodeHierarchy 
    where PK_NodeID in (@nodeid, @parentID)
    or FK_ParentNodeID = @nodeid

Конечно, вы можете поместить это в табличную функцию, чтобы сделать ее общей.

Answer 2

Самореференциальное представление иерархии немного неудобно для таких заданий, как - вы хотите выбрать только одну ветвь, поэтому вам нужно будет рекурсивно присоединяться к целевой таблице неизвестное количество раз. Очень возможно, но всякий раз, когда я работаю с иерархиями в SQL Server, я сразу перехожу к HierarchyId.

Я не знаю, сможем ли мы рекурсивно смотреть одновременно вверх и вниз по дереву; наивный подход терпит неудачу для меня, поэтому я представлю более простую альтернативу.

У вас уже есть текущий узел. Получить дочерние элементы этого узла, а затем получить родителей этого узла. Объедините их, и все готово. И самый простой способ сделать рекурсивные объединения в SQL - это Common Table Expressions.

DECLARE @nodeid INT = 4
DECLARE @nodes TABLE (NodeID INT)

; WITH Parents (NodeID) AS
(
    -- get the parent of the current node
    SELECT FK_ParentNodeID FROM NodeHierarchy WHERE PK_NodeID = @nodeId
        -- not sure if 'null' is a valid parent, but I'm assuming not
        AND FK_ParentNodeID IS NOT NULL
    UNION ALL
    -- recursively get the parents of the parent
    SELECT FK_ParentNodeID FROM NodeHierarchy 
        INNER JOIN Parents ON PK_NodeID = NodeID
        WHERE FK_ParentNodeID IS NOT NULL     
)
INSERT @nodes SELECT NodeID FROM Parents

; WITH Childs (NodeID) AS
(
    -- selecting the current node
    SELECT PK_NodeID FROM NodeHierarchy WHERE PK_NodeID = @nodeId
    UNION ALL
    -- recursively select the children of the branch
    SELECT PK_NodeID FROM NodeHierarchy 
        INNER JOIN Childs ON FK_ParentNodeID = NodeID
)
INSERT @nodes SELECT NodeID FROM Childs

SELECT * FROM @nodes

Теперь, основываясь на вашем предыдущем вопросе, вам просто нужно выбрать из существующих представлений.

SELECT Node, Label FROM NodeLabels 
WHERE Node IN (SELECT NodeID FROM @nodes)

SELECT Parent, Child FROM Edges
WHERE Parent IN (SELECT NodeID FROM @nodes)