Neo4j Query : Apply Multiple WHERE conditions not working

Question

I have query which is working fine. This gives me all the trains which currently has the fuelPercentLeft as more than 50%.

MATCH (t:Train) 
WITH t.trainId AS trainid, MAX(t.timestamp) AS maxTimeStamp
MATCH (n:Train{trainId: trainid, timestamp: maxTimeStamp})-[:HAS]->(m:Attributes{name:'fuelPercentLeft'}) 
WHERE toInteger(m.value) > 50
RETURN count(toInteger(m.value))

I want to use only one query to return multiple things. For example for all the trains RETURN all trains having fuelPercentLeft as more than 50, Engine Temperature is less than 20, Lubricant Level is less than 10. Is it possible to apply multiple WHERE conditions in above query? Can we do something as below ?

MATCH (t:Train) 
WITH t.trainId AS trainid, MAX(t.timestamp) AS maxTimeStamp
CALL {
MATCH (n:Train{trainId: trainid, timestamp: maxTimeStamp})-[:HAS]->(m:Attributes{name:'fuelPercentLeft'}) 
WHERE toInteger(m.value) > 50
RETURN count(toInteger(m.value)) AS goodFuel
}
CALL {
MATCH (n:Train{trainId: trainid, timestamp: maxTimeStamp})-[:HAS]->(m:Attributes{name:'fuelPercentLeft'}) 
WHERE toInteger(m.value) < 50
RETURN count(toInteger(m.value)) AS badFuel
}
CALL {
MATCH (n:Train{trainId: trainid, timestamp: maxTimeStamp})-[:HAS]->(m:Attributes{name:'engineTemp'}) 
WHERE toInteger(m.value) > 20
RETURN count(toInteger(m.value)) AS goodEngine
}
CALL {
MATCH (n:Train{trainId: trainid, timestamp: maxTimeStamp})-[:HAS]->(m:Attributes{name:'lubricantLevel'}) 
WHERE toInteger(m.value) < 10
RETURN count(toInteger(m.value)) AS badLubricant
}

RETURN goodFuel, badFuel, goodEngine, badLubricant

I tried this but it is not working as expected. The requirement is, one single query should be able to RETURN multiple values based on Multiple conditions.

Kindly help. Thanks!

Answer 1

You can use an optional match in the chain:

MATCH (t:Train) 
WITH t.trainId AS trainid, MAX(t.timestamp) AS maxTimeStamp

OPTIONAL 
MATCH (n:Train{trainId: trainid, timestamp: maxTimeStamp})-[:HAS]->(m:Attributes{name:'fuelPercentLeft'}) 
WHERE toInteger(m.value) > 50

WITH trainid, maxTimeStamp, count(m) AS goodFuel

OPTIONAL 
MATCH (n:Train{trainId: trainid, timestamp: maxTimeStamp})-[:HAS]->(m:Attributes{name:'fuelPercentLeft'}) 
WHERE toInteger(m.value) < 50

WITH trainid, maxTimeStamp, goodFuel, count(m) AS badFuel

OPTIONAL 
MATCH (n:Train{trainId: trainid, timestamp: maxTimeStamp})-[:HAS]->(m:Attributes{name:'engineTemp'}) 
WHERE toInteger(m.value) > 20

WITH trainid, maxTimeStamp, goodFuel, badFuel, count(m) AS goodEngine

OPTIONAL 
MATCH (n:Train{trainId: trainid, timestamp: maxTimeStamp})-[:HAS]->(m:Attributes{name:'lubricantLevel'}) 
WHERE toInteger(m.value) < 10

WITH trainid, maxTimeStamp, goodFuel, badFuel, goodEngine, count(m) AS badLubricant

RETURN trainid, maxTimeStamp, goodFuel, badFuel, goodEngine, badLubricant