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:
- nodeId
And Edge table has below Schema:
Edge Table Schema:
- startDate
- 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://learn.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:
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.