Cypher query to reach a terminal node

Question

I have the following data structure for a bus service. A city node has many buses to other city node which again has many buses to other city node.

Bus and city are uniquely identified by their id.

(a:city)-[r:To]->(b:City)->[r:To]->(c:City)-[r:To]->(d:City)

Node City has properties:cityId(int) relationship To has prop:busId(int),arrivalTime(int).

Question:

Given a cityId and a busId how to write a cypher query to get the route of the bus i.e all city from start to destination sorted by the arrivalTime.

The cityId provided above ensures that the bus starts from that city.

My Guess

Match (a:City)-[r:To*]->(b:City) where a.cityId=cityid and r.busId=busId return r,b. order by r.arrivalTime

Do you have any flexibility with the model? I think you may find the query easier to write with a different model. — Ben Butler-Cole
I don't understand the part "does not ensure b is the last station". Do you want just the time of arrival at the terminus (in which case sorting the results doesn't seem to helpful)? Or the arrivals at all the stations along the way (in which case you don't need to ensure that it's the last station)? — Ben Butler-Cole
'the arrivals at all the stations along the way'.I have already written the code.So it will be great if you can provide me query for this existing model.What's a better model than this? — lliB the Lizard
I think you can achieve what you need to with the current model (see below), but I think it might be simpler if you model the routes and legs explicitly as nodes. — Ben Butler-Cole

Ben Butler-Cole Ben Butler-Cole · Accepted Answer · 2014-04-30T15:26:29

I think that this will give you what you want (requires Neo4j 2.0.1 or later).

MATCH (c:City)-[legs:TO*]->(terminus)
WHERE c.cityId = { cityId } 
      AND ALL (leg IN legs WHERE leg.busId = { busId })
      AND NOT (terminus)-[:TO {busId: { busId }}]->()
RETURN [leg IN legs | 
        {city: endNode(leg).cityId, arrival: leg.arrivalTime}] 
       AS stops

Three things to note here.

When handling the legs we need to use collection operations because of the variable length paths.
Given a leg, we can extract information about the city it ends in with the endnode() function.
We use a negated pattern to identify the terminus of the route, otherwise we get a result for each possible path length.

Cypher query to reach a terminal node

1 Answers