Graph Extensions: SHORTEST_PATH query with a collection of nodes.

Miroslav Rokva 21 Reputation points
2022-03-25T11:15:41.407+00:00

I am using SQL Server graph extentions (node/edge table) to represent a social network. I know how to create a query that will map one person to the shortest path to several people, i.e. Find the shortest route between Alice and the list { Bob, Charles }:

Alice -- somebody -- Bob
Alice -- somebody -- somebody -- Charles

But I don't know how to map one collection of people to another, i.e. shortest path between {Alice,Denise} and {Bob, Charles}

Alice -- somebody -- Bob
Alice -- somebody -- somebody -- Charles
Denise -- Charles
Denise -- somebody -- somebody -- somebody -- somebody -- Bob

Obviously I can just do a union, but is there a way that allows me to do so with one single query?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,069 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 113.8K Reputation points
    2022-03-25T18:54:57.873+00:00

    I think that if you replace '=' with IN in Example A (https://learn.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-shortest-path?view=sql-server-ver15#examples), then it should work with multiple sources and targets, i.e. Person1.name IN ('Alice', 'Denice'), Q.LastNode IN ('Bob', 'Charles').

    To show the shortest path of found paths, try something like this:

    SELECT top(1) with ties PersonName, Friends, levels  
    FROM (  
    	SELECT  
    		Person1.name AS PersonName,  
    		STRING_AGG(Person2.name, '->') WITHIN GROUP (GRAPH PATH) AS Friends,  
    		LAST_VALUE(Person2.name) WITHIN GROUP (GRAPH PATH) AS LastNode,  
    		COUNT(Person2.name) WITHIN GROUP (GRAPH PATH) AS levels  
    	FROM  
    		Person AS Person1,  
    		friend FOR PATH AS fo,  
    		Person FOR PATH AS Person2  
    	WHERE MATCH(SHORTEST_PATH(Person1(-(fo)->Person2)+))  
    	AND Person1.name in ('Alice', 'Denice')  
    ) AS Q  
    WHERE Q.LastNode in ('Bob', 'Charles')  
    order by levels  
    
    1 person found this answer helpful.
    0 comments No comments

  2. Bert Zhou-msft 3,421 Reputation points
    2022-03-28T06:25:21+00:00

    Hi,@Miroslav Rokva

    Welcome to Microsoft T-SQL Q&A Forum!

    You may be able to try the above way of writing, but first you need to select @@version to check your database version.
    Currently the Graph Extensions (graph processing) you want is only supported in SQL Server 2019 (15.x) and Azure SQL Database, if the above The expert's writing method can't solve your problem. It is recommended that you use cte and traditional join implementation.
    Please refer to this link.

    Best regards,
    Bert Zhou


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments