'WITH RECURSIVE Not Working Correctly - Postgres
I have two tables representing a graph. Nodes consists of an id, type and properties. Edges consist of an id, properties, type and an origin node id and destination node id. I've been working on a query that will basically take a single node and return the graph to a given depth - heavily based (basically copied) on an article here - https://www.alibabacloud.com/blog/postgresql-graph-search-practices---10-billion-scale-graph-with-millisecond-response_595039
However, whenever I run the query my results are always returned with many duplicates of the first query, as if it's not running running the recursive part - I'm not sure what I'm doing wrong here.
Here is my query.
WITH RECURSIVE search_graph(
id,
origin_id,-- point 1
origin_metatype_id,
origin_properties,
destination_id, -- point 2
destination_metatype_id,
destination_properties,
relationship_pair_id,
properties, -- edge property
depth, -- depth, starting from 1
path -- path, stored using an array
) AS (
SELECT -- ROOT node query
g.id,
g.origin_id,-- point 1
n1.metatype_id AS origin_metatype_id,
n1.properties AS origin_properties,
g.destination_id, -- point 2
n2.metatype_id AS destination_metatype_id,
n2.properties AS destination_properties,
g.relationship_pair_id,
g.properties, -- edge property
1 as depth, -- initial depth =1
ARRAY[g.id] as path -- initial path
FROM current_edges AS g
LEFT JOIN nodes n1 ON n1.id = g.origin_id
LEFT JOIN nodes n2 ON n2.id = g.destination_id
WHERE
origin_id = ? -- ROOT node =?
UNION ALL
SELECT
g.id,-- recursive clause
g.origin_id,-- point 1
n1.metatype_id AS origin_metatype_id,
n1.properties AS origin_properties,
g.destination_id, -- point 2
n2.metatype_id AS destination_metatype_id,
n2.properties AS destination_properties,
g.relationship_pair_id,
g.properties, -- edge property
sg.depth + 1 as depth, -- depth + 1
sg.path || g.id as path -- add a new point to the path
FROM current_edges AS g
LEFT JOIN nodes n1 ON n1.id = g.origin_id
LEFT JOIN nodes n2 ON n2.id = g.destination_id,
search_graph AS sg -- circular INNER JOIN
WHERE
g.origin_id = sg.destination_id -- recursive JOIN condition
AND (g.id != ALL(sg.path)) -- prevent from cycling
AND sg.depth <= 10 -- search depth =?
)
SELECT * FROM search_graph;
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
