I have a sql graph db as follow:
Below is my sql script:
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'));
What I have try:
Following query that I have try only able to give me shortest path of each node that end with node C.
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'
What I intend to achieve:
- Find ALL path that start with node C
- Find ALL path that end with node C
The key thing here is that I want to find all path that start with C and end with C. I expect that I could achieve (2) by change the above query by changing SHORTEST_PATH to ALL_PATH but there is no such method exist. On the other hand, I have no idea of how to achieve (1).
How can I achieve what I wanted?
