First, sorry for my English. I am modeling a railways DB in neo4j. I want to link stations in the order that they are linked by the railway, using the stops table. Every stop owns a "stop sequence" that unfortunately isn't like 1,2,3 (not always) but only progressive like 1,3,4,6. I wrote this query that, for the problem described, doesn't always work.
MATCH (a:Station)-[:stop]->(c:Stops_times)-[:trip]->(z:Trips)<-[:trip]-(d:Stops_times)<-[:stop]-(b:Station) WHERE toint(c.stop_sequence)=toint(d.stop_sequence)+1 CREATE (a)-[s:next]->(b)
To find the right "next" I need a query similar to this:
MATCH (a:Station)-[:stop]->(c:Stops_times)-[:trip]->(z:Trips)<-[:trip]-(d:Stops_times)<-[:stop]-(b:Station) WITH c as c, d as d, MIN(d.stop_sequence) as min_ WHERE min_>c.stop_sequence CREATE UNIQUE (a)-[s:next]->(b)
therefore, for every stop, I have to found the minimum "stop_sequence" between the higher ones than the "stop_sequence" of the stop of which I want to find the next
The following query seems to do what you want. It orders all the stops by stop_sequence
, aggregates all the stops (still in order) for each trip, pairs up all adjoining stops for each trip, UNWIND
s the pairs so that MERGE
can use the paired nodes, and then uses MERGE
to ensure that the :next
relationship exists between all node pairs.
MATCH (a:Station)-[:stop]->(c:Stops_times)-[:trip]->(t:Trips)
WITH a, c, t
ORDER BY c.stop_sequence
WITH t, COLLECT(a) AS s
WITH REDUCE(x =[], i IN RANGE(1, SIZE(s)-1)| x + {a: s[i-1], b: s[i]}) AS pairs
UNWIND pairs AS p
WITH p.a AS a, p.b AS b
MERGE (a)-[n:next]->(b);
It works properly in 2.3.2 on my Mac (but the neo4j versions available at http://console.neo4j.org/ do not work correctly when the query gets to MERGE
).