How to visualize only the descendants, ancestors, and self of a node in a hierarchy?
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?
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)
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.
精彩评论