Reputation: 20509
I have a structure of graph tables containing 2 Node tables and 1 Edge table.
Symptom table is a Node table and Syndrome_Causes is an Edge table. Also, there is another Node table called Syndrome.
My goal is to have a query where I can traverse my graph, going through specific nodes.
I have written the below query to show you all possible routes, starting from the "Cardiovascular" node, but I would need to filter out the results going through the path, underlined with red in the following image (Cardiovascular->Palpitations->Duration->Interval->Days).
select distinct
s1.symptom_name
, string_agg(s2.symptom_name, '->') within group (graph path) AS links
, last_value(s2.symptom_name) within group (graph path) as last_match
from symptom as s1,
syndrome_causes for path as sc1,
symptom for path as s2
where match(
shortest_path(s1(-(sc1)->s2)+)
)
and s1.symptom_name = 'Cardiovascular'
I have thought about adding another filtering condition to the WHERE
clause, but I can't figure out how I could write it so that I constrain the node to go only through the values in the "links" column.
Mind you, I know and I can pass it the value in the links column to filter the rows and get me to the "Days" value in last_match
column, but my intention is to filter it by following a certain path through nodes (a verification), something like below:
where match(
shortest_path(s1(-(sc1)->s2)+)
)
and s1.symptom_name = 'Cardiovascular'
and s2.symptom_name = ALL('Palpitations, 'Duration', 'Interval', 'Days')
However, this gives out an error:
The alias or identifier 's2.symptom_name' cannot be used in the select list, order by, group by, or having context.
So, is there a way to constrain a path search to go through certain nodes?
Upvotes: 0
Views: 387
Reputation: 6808
So, is there a way to constrain a path search to go through certain nodes?
It is possible to filter/limit the nodes to be considered for the path, by selecting only specific values "for path", but this does not dictate how the path is traversed.
......
from symptom as s1,
syndrome_causes for path as sc1,
(select * from symptom where symptom_name in ('Palpitations', 'Duration', 'Interval', 'Days')) for path as s2
......
Upvotes: 1