CODEWITHSUNDEEP
sqlsql-servert-sqlgraph-algorithmsql-server-2019
ozz
ozz

Reputation: 183

Graph shortest path .. use only labelled edges?

With the new SQL Server version there is the function SHORTEST_PATH. I use attributes (or Labels) in the edge tables to distinguish the different types of connections.

Unfortunately the SHORTEST_PATH function doesn't seem to allow any attributes in the where condition (if the tables are marked for path)

SELECT
       l1.CommonName AS CommonName, 
       STRING_AGG(l2.CommonName, '->') WITHIN GROUP (GRAPH PATH) AS Verbindung,
       LAST_VALUE(l2.CommonName) WITHIN GROUP (GRAPH PATH) AS LastNode
 from           object as l1, 
                connections for path as v,
                object for path as  l2 
    where match(SHORTEST_PATH( l1  (-(v)-> l2)+))
    and l1.CommonName = 'jagdtWurst'
    and v.label= 'hierarchie' <<--- This is not possible .... Error

Is there a no trick to how to do that anyway?

Upvotes: 1

Views: 241

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89489

Looks like you can use a subquery in the from clause. eg

SELECT
       l1.CommonName AS CommonName, 
       STRING_AGG(l2.CommonName, '->') WITHIN GROUP (GRAPH PATH) AS Verbindung,
       LAST_VALUE(l2.CommonName) WITHIN GROUP (GRAPH PATH) AS LastNode
 from           object as l1, 
                (select * from connections where label = 'hierarchie') for path as v,
                object for path as  l2 
    where match(SHORTEST_PATH( l1  (-(v)-> l2)+))
    and l1.CommonName = 'jagdtWurst'

Upvotes: 2

Related Questions