I'm running neo4j community 4.3.2 on Ubuntu 20.04.3
I have the following query:
MATCH (product:Product {uuid: 'e418f3cd-c26e-483d-9915-acdb0408af79' })
MATCH (user:User {userID: '10227287152944531'})
MERGE (user)-[:HAS_CART]->(cart:Cart {uuid: 1})
MERGE (cart)-[contains:CONTAINS]->(product)
ON CREATE
SET contains.quantity = 1
ON MATCH
SET contains.quantity = contains.quantity + 1
WITH contains, contains.quantity as quantity
call {
WITH contains
WITH contains
WHERE contains.quantity = 0
DETACH DELETE contains
RETURN contains as nil
}
return quantity
This query perform exactly as expected: it creates the missing nodes and set the value of the contains.quantity property.
The problem is the return value: it is [] while I expect a quantity.
If I remove the subquery, the return value is correct.
I think it is due to your return statement in the sub query. When joined back to the outer query results, it is producing no rows since it is overwriting contains with nil. Try removing the return. It is not serving a purpose in the outer query anyway and a return statement in the sub query is not required.
It is intriguing. cypher issues are fun to figure out.
I ran your query in my browser. I got the same behavior with a return statement. I think what is happening is that the WHERE clause is not true, so the subquery's result is null. The null value is returned and joined to the outer query's result, resulting in a null result. You can see that a quantity value is returned when the condition is changed to be true, such as quantity > 0.
I also executed it with the subquery's RETURN being commented out, and it ran and returned the quantity. Not sure why we are getting difference behavior. I am using neo4j 4.4.4. Is yours the same?
I do have follow up question. How is the subquery condition every going to be true, since the value of quantity seems to have to be a positive value greater than zero. Could you have the 'delete product from cart' operation in a separate query called when the quantity is known to be zero?
I'm running the 4.3.2, and maybe this could explain the difference.
Well, in the real world the number could also go down to 0, so yes, I have already splitted the query in two parts, the second executing only when the quantity is = 0. And this solved the problem.
But in 4.3.2 the return statement looks mandatory.
We'll expect for a follow up, if any. This is why I suggested Cypher people to add some direct (easy) if-then-else or switch statement to the cypher language: the WITH alone is not enough!
MATCH (product:Product {uuid: 'e418f3cd-c26e-483d-9915-acdb0408af79' })
MATCH (user:User {userID: '10227287152944531'})
MERGE (user)-[:HAS_CART]->(cart:Cart {uuid: 1})
MERGE (cart)-[contains:CONTAINS]->(product)
ON CREATE
SET contains.quantity = 1
ON MATCH
SET contains.quantity = contains.quantity + 1
WITH contains, contains.quantity AS quantity
CALL {
WITH contains
WHERE contains.quantity = 0
DELETE contains
RETURN contains AS nil
}
WITH quantity, nil
RETURN quantity
You can also use APOC for this case:
MATCH (product:Product {uuid: 'e418f3cd-c26e-483d-9915-acdb0408af79' })
MATCH (user:User {userID: '10227287152944531'})
MERGE (user)-[:HAS_CART]->(cart:Cart {uuid: 1})
MERGE (cart)-[contains:CONTAINS]->(product)
ON CREATE
SET contains.quantity = 1
ON MATCH
SET contains.quantity = contains.quantity + 1
WITH contains, contains.quantity AS quantity
CALL apoc.do.when(
quantity = 0,
'DELETE contains',
'',
{contains:contains})
YIELD value
RETURN quantity
Importing WITH should consist only of simple references to outside variables. WHERE is not allowed. (line 12, column 5 (offset: 390))
" WITH contains"
^
The second returns the following error:
There is no procedure with the name `apoc.do.when` registered for this database instance. Please ensure you've spelled the procedure name correctly and that the procedure is properly deployed.
Maybe the latter depends on the version? I'm using the 4.3.2!
