SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU30) (KB5049235) - 15.0.4415.2 (X64) 
	Nov 18 2024 17:45:37 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Linux (Rocky Linux 8.10 (Green Obsidian)) <X64>


-- 
-- Graph Queries - great example from Stack Overflow thread:
-- https://stackoverflow.com/questions/59632206/sql-server-graph-query-find-all-paths-to-node
select current_timestamp;


(No column name)


2024-12-21 17:57:40.010


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'));
14 rows affected

-- Query 1
-- 
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';


userName
StartNode
FinalNode
Edges Path
Levels


C
C
A
A
1

C
C
E
A->E
2

C
C
F
A->F
2

C
C
B
A->E->B
3

C
C
C
A->E->C
3


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


username
StartNode
Edges Path
FinalNode
Levels


E
E
C
C
1

A
A
E->C
C
2

B
B
E->C
C
2

C
C
A->E->C
C
3

F
F
B->E->C
C
3


-- Query 3
-- Added new column FULL_PATH which contains the StartNode || Edges Path
--
SELECT
    username, StartNode, [Edges Path], FinalNode, Levels, 
    CONCAT(StartNode, '->', [Edges Path]) FullPath
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){1,4}))
 ) AS Q
 WHERE Q.FinalNode = 'C'


username
StartNode
Edges Path
FinalNode
Levels
FullPath


E
E
C
C
1
E->C

A
A
E->C
C
2
A->E->C

B
B
E->C
C
2
B->E->C

C
C
A->E->C
C
3
C->A->E->C

F
F
B->E->C
C
3
F->B->E->C
username	StartNode	Edges Path	FinalNode	Levels	FullPath
E	E	C	C	1	E->C
A	A	E->C	C	2	A->E->C
B	B	E->C	C	2	B->E->C
C	C	A->E->C	C	3	C->A->E->C
F	F	B->E->C	C	3	F->B->E->C