How to visualize only the descendants, ancestors,

问题:

In another question I asked about visualizing hierarchical data stored in a table in an SQL Server database. I found a way to visualize the whole hierarchy using GraphViz, with some plumbing in T-SQL and Powershell.

I want to use a visualization like this to debug an application that uses similar data. Visualizing everything is fine for the small example hierarchy. But in a hierarchy of thousands, this is overwhelming.

When I debug my application, I usually look at only a small set of nodes related to a given node. Currently, the only related nodes that matter to me for a given node are the descendants and the ancestors, as well as the node itself.

So, I want a way to visualize only the nodes in the hierarchy that are the descendants, ancestors, or self of a given node.

The following statements create the example database and table as in the linked question.

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
);

The following statement populates the table with a modified version of the hierarchy of countries, cities, and venues. The United Kingdom is now the root node, and there are more nodes to represent famous English venues.

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');

The following image is the output of Powershell script generate-graph.ps1 listed in the linked question. If the Stack Overflow reduced-size version looks ugly, look at the full-size image.

I want to see only how St Andrews' descendants and ancestors relate to it. The diagram contains a lot of information irrelevant to these relationships, and so is harder to read. When I scale my hierarchy up to thousands of nodes covering cities and venues globally, a full visualization becomes almost useless.

In Freemind I drew a crude diagram of what I would like to see instead:

How do I extract only the data that is relevant to St Andrews so I can give it to GraphViz?

回答1:

A self-referential representation of hierarchy is a bit clunky for jobs like - you want to select only one branch, so you'll need to recursively join onto the target table an unknown number of times. Very possible, but any time I work with hierarchies in SQL Server I jump straight to the HierarchyId.

I don't know if we can recursively look both up and down the tree at the same time; a naive approach fails for me, so I'll present a simpler alternative.

You already have the current node. Get the children of that node, and then get the parents of that node. Union them and you're done. And the easiest way to do recursive joins in SQL is with 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

Now based on your previous question, you simply need to select from your existing views.

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

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

回答2:

I don't think you heed to use union here, it is a way more simple:

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

Of course, you can put it in a table function to make it general.