In the following cypher query, whenever the $dlt parameter is false, the query never continues beyond the DETACH DELETE statement:
MATCH (person:Person {id: $id})
SET person.matched = (CASE person.secret WHEN $secret THEN 1 ELSE 0 END)
WITH person WHERE person.matched = 1 AND $dlt = true
MATCH (person)<-[:UsedBy]-(dev:Device)
DETACH DELETE dev
WITH person WHERE person.matched = 1
MERGE (person)<-[r:UsedBy {assignedDate: dateTime()}]-(device:Device {id: 'efgh', firebaseToken: 'jjjj8888'})
WITH person, person.matched as matched
REMOVE person.matched
RETURN
CASE matched
WHEN 0
THEN null
ELSE person END AS Person
The idea is that all Device nodes (and connecting edges) needs to be removed only when the $dlt is true. However, regardless of $dlt (and this is what is not happening) - the subsequent parts must continue (adding a new Device node with a connecting edge, deleting the temporary matched property from person and returning based on matched value).
Btw, I'm running this query from a Jupyter Notebook against an AWS Neptune DB, with the %%oc magic command on top. As this is just for testing, I am not really using parameters (e.g. $dlt) in the Jupyter Notebook, but rather hard-coding some values.
What am I missing?
[UPDATED]
Since openCypher is a limited version of Cypher, the simplest thing would be to split this particular query in two: do the optional deletion in one query and then the optional merge in another. If the 2 queries should be done together atomically, you can use a Mutation Bolt transaction query, and that transaction code can return the appropriate value after it performs both individual queries.
Or you can explore clever ways to use OPTIONAL MATCH
, FOREACH
, and so forth. This question and its answer may be instructive.
You can use CALL subqueries to do conditional processing that does not abort the rest of the query.
For example, the following version of your query might work for you. It uses "unit subqueries". A unit subquery does not return anything and does not affect the rows being processed by the enclosing query.
Note that I also simplified your query by not bothering to set and then remove the temporary matched
property on each Person
, which is wasteful of time and resources. Also, there was no reason to treat booleans as integers.
MATCH (person:Person {id: $id})
WITH person, (person.secret = $secret) AS matched
CALL {
WITH person, matched
WITH person, matched
WHERE matched AND $dlt
MATCH (person)<-[:UsedBy]-(dev:Device)
DETACH DELETE dev
}
CALL {
WITH person, matched
WITH person, matched
WHERE matched
MERGE (person)<-[r:UsedBy {assignedDate: dateTime()}]-(device:Device {id: 'efgh', firebaseToken: 'jjjj8888'})
}
RETURN (CASE WHEN matched THEN null ELSE person END) AS Person
As a bonus, the following is an simple example of how to use post-union processing to do "if/else" processing that does not abort the rest of the enclosing query. In this example, the subquery is not a "unit subquery" and returns a type
variable that is visible to the enclosing query.
UNWIND [1,2,3,4,5] AS x
CALL {
WITH x
WITH x
WHERE x % 2 = 0
RETURN 'even' AS type
UNION
WITH x
WITH x
WHERE x % 2 <> 0
RETURN 'odd' AS type
}
WITH x, type, x^2 AS squared //arbitrary post-subquery processing
RETURN x, type, squared
ORDER BY x