在 SQL Graph 資料表中找某個 Node 的所有 Path

前言

最近將資料存到 MSSQL 的 Graph Table 之中，
想要查到某個 Node 在所有關系中路徑，例如下圖 C 的所有 Path。
剛好跟這篇sql server graph query, find all paths to node的問題相同。

解法

依sanora提供的範例可以分別取得 開始為 C 的整個路徑 及 結束為 C 的整個路徑。
初始資料如下，

DROP TABLE IF EXISTS Person;
CREATE TABLE Person (userName VARCHAR(100)  PRIMARY KEY) AS NODE;

INSERT INTO Person (userName) VALUES ('A'),('B'),('C'),('D'),('E'),('F');

DROP TABLE IF EXISTS Follow;
CREATE TABLE Follow AS EDGE;

--建立如上圖的關係資料
INSERT INTO Follow ($from_id, $to_id) VALUES (
   (SELECT $node_id FROM dbo.Person WHERE userName = 'A'),
   (SELECT $node_id FROM dbo.Person WHERE userName = 'E')),

   ((SELECT $node_id FROM dbo.Person WHERE userName = 'E'),
   (SELECT $node_id FROM dbo.Person WHERE userName = 'C')),

   ((SELECT $node_id FROM dbo.Person WHERE userName = 'C'),
   (SELECT $node_id FROM dbo.Person WHERE userName = 'A')),

   ((SELECT $node_id FROM dbo.Person WHERE userName = 'A'),
   (SELECT $node_id FROM dbo.Person WHERE userName = 'F')),

   ((SELECT $node_id FROM dbo.Person WHERE userName = 'F'),
   (SELECT $node_id FROM dbo.Person WHERE userName = 'B')),

   ((SELECT $node_id FROM dbo.Person WHERE userName = 'B'),
   (SELECT $node_id FROM dbo.Person WHERE userName = 'F')),

   ((SELECT $node_id FROM dbo.Person WHERE userName = 'B'),
   (SELECT $node_id FROM dbo.Person WHERE userName = 'E')),

   ((SELECT $node_id FROM dbo.Person WHERE userName = 'E'),
   (SELECT $node_id FROM dbo.Person WHERE userName = 'B'));

要找路徑的話，可以透過 SHORTEST_PATH 這個 Fuction ，
並給它要查詢的 Node 及 Edge Pattern ，
例如 Query 1 ，找開始節點為 C (p1.userName = 'C')的所有路徑(使用 +)，

SELECT
        p1.userName,
        p1.userName as StartNode,
        LAST_VALUE(p2.userName) WITHIN GROUP (GRAPH PATH) AS FinalNode,
        STRING_AGG(p2.userName,'->') WITHIN GROUP (GRAPH PATH) AS [Edges Path],
        COUNT(p2.userName) WITHIN GROUP (GRAPH PATH) AS Levels
    FROM
        dbo.Person p1,
        dbo.Person FOR PATH p2,
        dbo.Follow FOR PATH Follow
    WHERE
        MATCH(SHORTEST_PATH(p1(-(Follow)->p2)+))
        AND p1.userName = 'C';

結果如下，

再來要找出最後節點為 C 的所有路徑，
因為最後節點要 Search 過之後才會知道，
所以這裡用 SubQuery 後，再來過濾 FinalNode = 'C'

-- Query 2

-- To find all the incoming connection for a node,
-- we need to wrap the query and filter for the final node like below:
--
-- NOTE: There is a TYPO in the SO thread. The regex '+' is missing from SHORTEST_PATH.
-- See above Query 1 for the correct syntax
--
SELECT
    username, StartNode, [Edges Path], FinalNode, Levels
FROM (
    SELECT
        P1.username,
        P1.username as StartNode,
        STRING_AGG(P2.userName,'->') WITHIN GROUP (GRAPH PATH) AS [Edges Path],
        LAST_VALUE(P2.userName) WITHIN GROUP (GRAPH PATH) AS FinalNode,
        COUNT(P2.userName) WITHIN GROUP (GRAPH PATH) AS Levels
    FROM
        Person P1,
        Person FOR PATH P2,
        Follow FOR PATH Follow
    WHERE
        MATCH(SHORTEST_PATH(P1(-(Follow)->P2)+))
 ) AS Q
 WHERE Q.FinalNode = 'C'

結果如下，

所以如果要 C 在關係中的所有路徑，就將它們 Union 起來就可以了，

SELECT
        p1.userName,
        p1.userName as StartNode,
        LAST_VALUE(p2.userName) WITHIN GROUP (GRAPH PATH) AS FinalNode,
        STRING_AGG(p2.userName,'->') WITHIN GROUP (GRAPH PATH) AS [Edges Path],
        COUNT(p2.userName) WITHIN GROUP (GRAPH PATH) AS Levels
    FROM
        dbo.Person p1,
        dbo.Person FOR PATH p2,
        dbo.Follow FOR PATH Follow
    WHERE
        MATCH(SHORTEST_PATH(p1(-(Follow)->p2)+))
        AND p1.userName = 'C'
UNION
SELECT
    username, StartNode, [Edges Path], FinalNode, Levels
FROM (
    SELECT
        P1.username,
        P1.username as StartNode,
        STRING_AGG(P2.userName,'->') WITHIN GROUP (GRAPH PATH) AS [Edges Path],
        LAST_VALUE(P2.userName) WITHIN GROUP (GRAPH PATH) AS FinalNode,
        COUNT(P2.userName) WITHIN GROUP (GRAPH PATH) AS Levels
    FROM
        Person P1,
        Person FOR PATH P2,
        Follow FOR PATH Follow
    WHERE
        MATCH(SHORTEST_PATH(P1(-(Follow)->P2)+))
 ) AS Q
 WHERE Q.FinalNode = 'C'

結果如下，

另外，本來要針對 Levels 來過濾，但發現查詢速度會變慢，
例如原本近 0 秒結果會出來，加上 Levels 來過濾會變成 4~10 秒。

參考資料

sql server graph query, find all paths to node