sql server graph query, find all paths to node

Question

I have a very common problem I am trying to solve using graph queries (sql server 2017).

I want to build a query and find how anyone in the nodes is connected to C.
I want to build a query and find how anyone in the nodes is connected to C (with 1 or 2 connections).

here is the full script to create this graph:

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

this query is not working since it is giving me only the direct relation:

SELECT Person1.userName as userName1, Person2.userName as userName2   
FROM Person as Person1, Follow, Person as Person2 
WHERE MATCH(Person1-(Follow)->Person2)
AND Person2.userName = 'C'

For sql 2017 you'll have to use a recursive cte. For sql 2019 you could use the answer provided by satishcse — lptr
– lptr, Commented Jan 8, 2020 at 8:44
@lptr could you pls share how to do it with cte? I have sql 2017. Much appreciated! — DmitriBodiu
– DmitriBodiu, Commented Apr 3, 2020 at 18:01

sacse · Accepted Answer · 2021-09-16 11:36:14Z

7

you can try something like below:

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

to find all the incoming connection for a node, we need to wrap the query and filter for the final node like below:

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'

to limit the levels or the number of hops, we can provide the recursion quantifiers in place of (+ --- one or more) like below:

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){1,3}))
 ) AS Q
 WHERE Q.FinalNode = 'C'

edited Sep 16, 2021 at 11:36

answered Jan 8, 2020 at 4:03

sacse

3,8272 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

SexyMF Over a year ago

Thank you and sorry for the late reply. I had problems installing SQL server 2019. Any in your query you are showing all C outgoing connections. I also want to know all C incoming connections.for example: A->E->C or F->B->E->C possible? I dont mind to have multiple queries. thanks

sacse Over a year ago

@SexyMF I have updated the answer to cover the scenarios.

Sanora Over a year ago

DBFiddle Link for above great example: dbfiddle.uk/…

Costas Feb 8 at 9:39

This does not find ALL paths, but only the first as per shortest_path documentation. Currently (SQLServer 2022) does not support multiple or all paths between two nodes. For example, if you have A->B->C and A->D->C, which have both equal path length, this query will return only one of the possible paths between A and C.

sacse Feb 13 at 8:19

@Costas you could use recursive CTE to get the all paths b/w any two nodes. -> example

|

Collectives™ on Stack Overflow

sql server graph query, find all paths to node

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related