Head's Up! These forums are read-only. All users and content have migrated. Please join us at community.neo4j.com.
06-27-2021 10:00 AM
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?
Solved! Go to Solution.
06-28-2021 03:15 AM
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)
06-28-2021 03:15 AM
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)
06-28-2021 03:52 AM
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)"
06-29-2021 12:43 AM
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
06-29-2021 12:50 AM
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...
06-29-2021 02:06 AM
Can you try to remove // when optional null
?
Or to add a \n
after this?
06-29-2021 03:46 AM
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)?
06-30-2021 12:44 AM
If it is a possible condition, personally it's better to use 2 separate queries to avoid additional checks due to "OPTIONAL MATCH"
06-28-2021 04:33 AM
Looks like the simplest way is to execute the second query to count the affected records ?
All the sessions of the conference are now available online