Correct RETURN statement for the query

Neo4j Graph Platform Cypher
1

I use the following query for sft delete fucntionality:

MATCH (be:BaseEntity) WHERE
be.uuid = $uuid AND be.deleted = false
SET be.deleted = true WITH be
OPTIONAL MATCH p=(be)-[:CONTAINS*]->(e)
WHERE e.deleted = false AND all(r IN relationships(p) WHERE r.associationType = $associationType)
WITH nodes(p) AS ns UNWIND ns as en
SET en.deleted = true
RETURN count(*) as n

with help of this query I mark nodes like deleted = true
I'd like to know how much nodes where marked as deleted (be + en) - how to properly do it?

With the following return statement RETURN count(*) as n some queries return 0 but this is not the correct result. How to properly change RETURN statement to properly count affected (be + en)?

UPDATED

I tried the following

MATCH (be:BaseEntity {deleted: false})
WHERE be.uuid = $uuid
SET be.deleted = true, be.deletedTxUuid = $deletedTxUuid
WITH be, count(be) as countBe
OPTIONAL MATCH p=(be)-[:CONTAINS*]->(e {deleted: false})
WHERE all(r IN relationships(p) WHERE r.associationType = $associationType)
WITH countBe, nodes(p) AS ns
UNWIND ns as en
SET en.deleted = true, en.deletedTxUuid = $deletedTxUuid
RETURN countBe + count(en) as n

but it also doesn't work. Looks like UNWIND killing the countBe variable value.
What am I doing wrong and how to fix it?

2

Maybe the result 0 happens when the optional is null.
Moreover, with nodes (p) the :BaseEntity is considered twice.

Maybe, you could do something like:

MATCH (be:BaseEntity)
WHERE  be.uuid = $uuid AND be.deleted = false
SET be.deleted = true 
WITH be
OPTIONAL MATCH p=(be:BaseEntity)-[:CONTAINS*]->(e)
WHERE all(r IN relationships(p) WHERE r.associationType = $associationType)
unwind coalesce(nodes(p), [null]) as nodes // when optional null
with collect(distinct nodes) as nodes, be
unwind case when nodes = [] then [null] else nodes end as node    // when optional null
with node, be
where node is null or node.deleted = false set node.deleted = true
with distinct be, collect(node) as node       // group by "be"
return count(be) + size(node)
1 Like
3

Thanks for your answer! Unfortunatly the query fails with the following exception:

org.neo4j.driver.exceptions.ClientException: Query cannot conclude with UNWIND (must be RETURN or an update clause) (line 1, column 259 (offset: 258))
"MATCH (be:BaseEntity) WHERE be.uuid = $uuid AND be.deleted = false SET be.deleted = true, be.deleteTxUuid = $deleteTxUuid WITH be OPTIONAL MATCH p=(be:BaseEntity)-[:CONTAINS*]->(e) WHERE all(r IN relationships(p) WHERE r.associationType = $associationType) unwind coalesce(nodes(p), [null]) as nodes // when optional null with collect(distinct nodes) as nodes, be unwind case when nodes = then [null] else nodes end as node with node, be where node is null or node.deleted = false set node.deleted = true, node.deleteTxUuid = $deleteTxUuid with distinct be, collect(node) as node return count(be) + size(node)"

4

Looks like the simplest way is to execute the second query to count the affected records ?

5

@myshareit

Based on the ClientException, maybe there seems to be a syntax error in your query.
After // when optional null there is a need for a new line

6

This is the query:

private static final String DELETE_CYPHER_QUERY_UUID = "MATCH (be:BaseEntity) " +
        "WHERE be.uuid = $uuid AND be.deleted = false " +
        "SET be.deleted = true, be.deleteTxUuid = $deleteTxUuid " +
        "WITH be " +
        "OPTIONAL MATCH p=(be:BaseEntity)-[:CONTAINS*]->(e) " +
        "WHERE all(r IN relationships(p) WHERE r.associationType = $associationType) " +
        "unwind coalesce(nodes(p), [null]) as nodes // when optional null " +
        "with collect(distinct nodes) as nodes, be " +
        "unwind case when nodes = [] then [null] else nodes end as node " +
        "with node, be " +
        "where node is null or node.deleted = false set node.deleted = true, node.deleteTxUuid = $deleteTxUuid  " +
        "with distinct be, collect(node) as node " +
        "return count(be) + size(node)";

and this is the error:

org.neo4j.driver.exceptions.ClientException: Query cannot conclude with UNWIND (must be RETURN or an update clause) (line 1, column 258 (offset: 257))

Looks like something related to UNWIND operation...

7

Can you try to remove // when optional null?
Or to add a \n after this?

8

Thank you ! Finally, I got what you mean! The following query works like a charm:

     "MATCH (be:BaseEntity) " +
        "WHERE be.uuid = $uuid AND be.deleted = false " +
        "SET be.deleted = true, be.deleteTxUuid = $deleteTxUuid " +
        "WITH be " +
        "OPTIONAL MATCH p=(be:BaseEntity)-[:CONTAINS*]->(e) " +
        "WHERE all(r IN relationships(p) WHERE r.associationType = $associationType) " +
        "unwind coalesce(nodes(p), [null]) as nodes  " +
        "with collect(distinct nodes) as nodes, be " +
        "unwind case when nodes = [] then [null] else nodes end as node " +
        "with node, be " +
        "where node is null or node.deleted = false set node.deleted = true, node.deleteTxUuid = $deleteTxUuid  " +
        "with distinct be, collect(node) as node " +
        "return count(be) + size(node) as n";

Thanks again!!

Also, what do you think - what is better - to use such single query but with additional logic inside ? Or use the following query:

    "MATCH (be:BaseEntity {deleted: false}) " +
        "WHERE be.uuid = $uuid " +
        "SET be.deleted = true, be.deleteTxUuid = $deleteTxUuid " +
        "WITH be " +
        "OPTIONAL MATCH p=(be)-[:CONTAINS*]->(e {deleted: false}) " +
        "WHERE all(r IN relationships(p) WHERE r.associationType = $associationType) " +
        "WITH nodes(p) AS ns UNWIND ns as en " +
        "SET en.deleted = true, en.deleteTxUuid = $deleteTxUuid"

for update and then an additional query to count affected nodes(by indexed property)?

9

If it is a possible condition, personally it's better to use 2 separate queries to avoid additional checks due to "OPTIONAL MATCH"

1 Like
10

@giuseppe_villan Thank you very much!