question

YamNg-0969 avatar image
1 Vote"
YamNg-0969 asked Viorel-1 edited

Graph Database "SHORTEST_PATH" with edge filtering

Hi all,

Currently I am trying to use the Graph DB functionality,

which in case my node table has an ID column:
Node Table Schema:

  1. nodeId

And Edge table has below Schema:
Edge Table Schema:

  1. startDate

  2. endDate

Which denote the start/end date for the edge relationship.

My question is that when using SHORTEST_PATH function, it seems there is no provided method for using edge which is effective currently (i.e., today >= startDate && today < endDate), may I ask if have any solution for the mentioned issue?

My SQL query:

 select node_1.nodeId
    STRING_AGG(node_2.nodeId, '->') WITHIN GROUP (GRAPH PATH) AS node_path,
    LAST_VALUE(node_2.nodeId) WITHIN GROUP (GRAPH PATH) AS descendant_node
 from node_tb as node_1,
  edge_tb for PATH as edge,
  node_tb FOR PATH as node_2
 where match(SHORTEST_PATH(node_1(-(edge)->node_2)+))


Thanks a lot.



Updates (2021/10/07):

Using the example from (with a bit amendment): https://docs.microsoft.com/en-us/sql/t-sql/queries/match-sql-graph?view=sql-server-ver15

 CREATE TABLE dbo.Person (ID INTEGER PRIMARY KEY, name VARCHAR(50)) AS NODE;
 CREATE TABLE dbo.friend (start_date DATE, end_date Date) AS EDGE;
    
 -- Insert into node table
 INSERT INTO dbo.Person VALUES (1, 'Alice');
 INSERT INTO dbo.Person VALUES (2,'John');
 INSERT INTO dbo.Person VALUES (3, 'Jacob');
 INSERT INTO dbo.Person VALUES (4, 'Tom');
    
 -- Insert into edge table
 INSERT INTO dbo.friend VALUES ((SELECT $node_id FROM dbo.Person WHERE name = 'Alice'),
        (SELECT $node_id FROM dbo.Person WHERE name = 'Jacob'), '20210901', '20211001');

 INSERT INTO dbo.friend VALUES ((SELECT $node_id FROM dbo.Person WHERE name = 'Alice'),
        (SELECT $node_id FROM dbo.Person WHERE name = 'John'), '20210901', NULL);

 INSERT INTO dbo.friend VALUES ((SELECT $node_id FROM dbo.Person WHERE name = 'Jacob'),
        (SELECT $node_id FROM dbo.Person WHERE name = 'John'), '20210901', NULL);

 INSERT INTO dbo.friend VALUES ((SELECT $node_id FROM dbo.Person WHERE name = 'John'),
        (SELECT $node_id FROM dbo.Person WHERE name = 'Tom'), '20210901', NULL);


I want to achieve to searched recursively for the person's social network at the current moment (i.e., what's are their friends and the person's friend at that moment, considering dbo.friend start_dt and end_dt)

Current node structure according to the above example:
138319-image.png

I can filter the edge using edge properties when I retrieve just one level of friend (using Alice as example):

 select person_2.name, 
 from Person as person_1,
   friend as hasFriendship,
   Person as person_2
 where match(person_1-(hasFriendship)->person_2)
 and person_1.name = 'Alice'
 and CURRENT_TIMESTAMP >= hasFriendship.start_dt 
 and (CURRENT_TIMESTAMP < hasFriendship.end_dt or hasFriendship.end_dt is null)


But considering the case of transitive closure syntax (which uses "SHORTEST_PATH" function), i.e.,

  select person_1.name,
     STRING_AGG(person_2.name, '->') WITHIN GROUP (GRAPH PATH) AS node_path,
     LAST_VALUE(person_2.name) WITHIN GROUP (GRAPH PATH) AS descendant_node
  from Person as person_1,
   friend for PATH as hasFriendship,
   Person FOR PATH as person_2
  where match(SHORTEST_PATH(person_1(-(hasFriendship)->person_2)+))
  and person_1.name = 'Alice';

I can't do the filer (i.e., where clause) for the edge table (i.e., friend table). Resulting error on finding "SHORTEST_PATH" based on dataset not considering friend start/end date.

May I ask if there are workarounds for the issue?


Expected Result:

I want to add the "edge properties filtering" in the query which do the transitive closure, i.e.,

      select person_1.name,
         STRING_AGG(person_2.name, '->') WITHIN GROUP (GRAPH PATH) AS node_path,
         LAST_VALUE(person_2.name) WITHIN GROUP (GRAPH PATH) AS descendant_node
      from Person as person_1,
       friend for PATH as hasFriendship,
       Person FOR PATH as person_2
      where match(SHORTEST_PATH(person_1(-(hasFriendship)->person_2)+))
      and person_1.name = 'Alice'
     --> and CURRENT_TIMESTAMP >= hasFriendship.start_dt 
     --> and (CURRENT_TIMESTAMP < hasFriendship.end_dt or hasFriendship.end_dt is null)

But since the "SHORTEST_PATH" operation disallow the filtering, I can't find a way to achieve the goal without writing a recursion. I want to see I missed something from the doc in order to achieve my goal in 1 SQL query.







sql-server-generalsql-server-transact-sql
image.png (8.9 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Good day and welcome to the QnA forum,

may I ask if have any solution for the mentioned issue?

Please provide (1) queries to create the tables and insert some sample data for the sake of the discussion and in order to reproduce the scenario. (2) In addition please explain what is the expected result according to the sample data.

0 Votes 0 ·

Thanks for adding the DDL+DML. Well done.
+1

Can you please provide the expected result set according to the sample (you query works for me and I am not sure what is missing).

Notice! In your edge table you have also a relation between Alice and Jacob. Is this a mistake? This not fit your image and break the structure of Childs/fathers

0 Votes 0 ·

That's my design for the edge table, which included an expiry time (start date, end date) for the edge

0 Votes 0 ·

Replacing ‘friend for path’ with ‘(select * from friend where …) for path’ is accepted, but the query seems to run several seconds, giving an error (“out of internal resources”).

As a workaround, maybe you can build another edge table, which contains the filtered rows only. However, temporary tables cannot be currently used.

0 Votes 0 ·

0 Answers