Get the sortest path

[undefined21]

I have a trouble returning the desired data because I might not understand some details.

Domain: I have a cars and each car delivers orders. But at some point of the route, the car has to refuel. I would like to find the first refuel gas station (in that case the red path)

With that cypher, I get the sortest path

MATCH path = (car:Car)-[:DELIVER*]->(city:City)-[:REFUEL]->(gas:GasStation)
// Transform the path length in table
UNWIND length(path) as hopsTable
return min(hopsTable)

But after, I want to get the gas station but I get the hops to each gas station. It seems that min function does not have effect...

MATCH path = (car:Car)-[:DELIVER*]->(city:City)-[:REFUEL]->(gas:GasStation)
// Transform the path length in table
UNWIND length(path) as hopsTable
WITH min(hopsTable) as minPage, gas
RETURN gas

But for example using LIMIT clause I get the first gas station:

MATCH path = (car:Car)-[:DELIVER*]->(city:City)-[:REFUEL]->(gas: GasStation)
WITH length(path) as hops, gas
RETURN p
ORDER BY hops ASC
LIMIT 1

Is there some way to do more efficiently or that one would be the right way to return the first gas station?

Thanks for your time!

[andrew_bowman · Accepted solution]

The last approach, ordering by the path length with a limit, is the one I'd recommend here.

As you saw, using min() or max() makes it hard to obtain the thing that the min or max is associated with.

We do have an APOC aggregation function, apoc.agg.minItems() (and a similar one for maxItems()) that lets you preserve the thing that is associated with the value:

MATCH path = (car:Car)-[:DELIVER*]->(city:City)-[:REFUEL]->(gas: GasStation)
WITH apoc.agg.minItems(gas, length(path)) as minData
RETURN minData.value as hops, minData.items as nearestStations

That works if there are several stations at the same min distance away. If you only need one of those, then just pick the first element in the items list.

[undefined21]

Woah! thats powerful function, I still need to dig more with apoc functions...

Thanks Andrew for your time!

[undefined21]

Hi again,

I am not able to run the above apoc function (apoc.agg.minItems(gas, length(path))), it throws me the following error:

Failed to invoke function 'apoc.agg.minItems': Caused by: java.lang.ArrayIndexOutOfBoundsException: Index 2 out of bounds for length 2

It seems, it cannot find the index but I do not understand why...

Now other question, if I run inside the apoc.case or apoc.when the LIMIT clause, it returns all the gas stations that are in that path and not the shortest path, as we get above (the first question block cypher). Could it be possible that apoc.case and apoc.when functions process one by one each path?

OPTIOANL MATCH path = (car:Car)-[:DELIVER*]->(city:City)-[:REFUEL]->(gas: GasStation)
// Check if the car does refuel since it starts. If it refuels get the first one
CALL apoc.case(
  [length(path) IS NULL, "RETURN NULL"], 
  "WITH length(path) as hops, gas RETURN gas ORDER BY hops ASC LIMIT 1",
  { gas: gas, path: path }
) YIELD value
RETURN value

Thanks for your time!

[andrew_bowman]

Could we see the full query using apoc.agg.minItems()? Also, did you execute in the browser to test this, and if not what did you use? Can you also try testing with cypher-shell?

You're right, in general (excepting aggregations, ordering, limiting, and others) clauses execute per row, so this usage of apoc.case() won't work, since the CALL itself is being executed per row.

You shouldn't need conditional cypher here. Try this:

OPTIONAL MATCH path = (car:Car)-[:DELIVER*]->(city:City)-[:REFUEL]->(gas: GasStation)
WITH length(path) as hops, gas 
RETURN gas 
ORDER BY hops ASC 
LIMIT 1

If there was no such path, gas will be returned as null.

[undefined21]

Thanks Andrew for the clarification about clauses.

Talking about apoc.agg.minItems(), this is the results what I get in the graph:

The neo4jBrowser output would be that one:

and the cypher-shell one, looks that the output is the same as browser one:

I do not know what I am missing...

Thanks for your time and help!

[andrew_bowman]

Thanks, that is an odd exception, could be buggy, or related to some previously fixed bug.

Can you verify which version of Neo4j and which version of APOC are being used?

[undefined21]

Neo4j v4.2.3 enterprise edition, APOC v4.2.0.4 and Neo4j Desktop 1.4.5.

Thanks for your time!

[andrew_bowman]

Okay, I see what's happening. This is a PIPELINED runtime bug, and it's already been patched in Neo4j 4.2.4.

I'd recommend staying current with patch releases within your minor release (4.2.8 is the latest 4.2.x patch) to avoid already-fixed bugs.