Setting new node Label is not working with detach delete

[Amit_Yadav]

I have two type of node with label "Wholesaler", "Retailer". "Wholesaler" will be connected to "Retailer" with "LINKED_TO" relationship.
When we will delete all the connected Retailers from "Wholesaler", we want to set the Wholesaler node label with "Retailer" along with previous Label.

I'm using below query to set the label but it's not working, anything I'm doing wrong here. But it will work, If I send one retailer visualId at a time.

MATCH (r:Retailer)

WHERE r.visualId IN ["f2777f03-1cb4-4eca-a918-805a083bf2ab", "2a32e695-5fbb-4470-87d9-605bb13dfe57"]

OPTIONAL MATCH(r)<-[:LINKED_TO]-(ws:Wholesaler)

OPTIONAL MATCH(ws)-[:LINKED_TO]->(retailers:Retailer)

WITH count(retailers) as retailersCount, ws, r

CALL apoc.do.when(retailersCount = 1, 'SET ws:Retailer return ""', '', {ws:ws}) YIELD value as tempValue

DETACH DELETE r

return ''"

1. Data creation query:

CALL apoc.create.node(["Wholesaler"], {name: "Tim Services", visualId: apoc.create.uuid()}) YIELD node as wholesaler
CALL apoc.create.node(["Retailer"], {name: "D-mart", visualId: apoc.create.uuid()}) YIELD node as retailer1
CALL apoc.create.node(["Retailer"], {name: "Reliance mart", visualId: apoc.create.uuid()}) YIELD node as retailer2
CALL apoc.create.relationship(wholesaler, "LINKED_TO", {}, retailer1)
YIELD rel as rel1
CALL apoc.create.relationship(wholesaler, "LINKED_TO", {}, retailer2)
YIELD rel as rel2
return ""

[glilienfield]

I think it is not working because the value of retailersCount is equal to 2, not 1. This is because of the way you wrote the query. In the following, line 1 will result in the single Wholesaler node, while the result of line 2 is actually two rows (not one as you expected), one for each Retailer attached to Wholesaler node. I believe this is not what you expected based on what you are trying to do. 

OPTIONAL MATCH(r)<-[:LINKED_TO]-(ws:Wholesaler)
OPTIONAL MATCH(ws)-[:LINKED_TO]->(retailers:Retailer)

Merging the two matches into one match will resolve this. I think the following works. I switched to use a call subquery for the conditional logic versus the apoc method, but you can use what you prefer.  The main fix is merging the two matches into one match.

MATCH (r:Retailer)
WHERE r.visualId IN ["c6500324-a0b4-4c25-9b11-5cb51c051078"]
OPTIONAL MATCH(r)<-[:LINKED_TO]-(ws:Wholesaler)-[:LINKED_TO]->(retailers:Retailer)
WITH count(retailers) as retailersCount, ws, r
call {
    with ws, retailersCount
    with ws, retailersCount
    where retailersCount = 1
    SET ws:Retailer
}
DETACH DELETE r

[Amit_Yadav]

@glilienfield I tried deleting more than 2 Retailer nodes and it's not working. @glilienfield  Can you help!!

[glilienfield]

The code you provided is constrained to adding the label only when there is one related Retailer node.  What do you want to do that is different?  If you can describe your full requirement and data model would be helpful for me to help?

[Amit_Yadav]

@glilienfield 

Requirement is to delete the given Retailer nodes and If the parent node (wholesaler) doesn't have any Retailer nodes then it should be labeled as "Retailer " along with previous label. Above query works if wholesaler(parent node) has only 2 or 1 node and we try to delete all of them togather. But it doesn't work if It has more than 2 nodes. And we try to delete all of them.

You can try to create data with below query and try to delete all the Retailer node. After deleting those, Wholesaler node will be labeled as "Retailer", "Wholesaler"::

CALL apoc.create.node(["Wholesaler"], {name: "Tim Services", visualId: apoc.create.uuid()}) YIELD node as wholesaler
CALL apoc.create.node(["Retailer"], {name: "D-mart", visualId: apoc.create.uuid()}) YIELD node as retailer1
CALL apoc.create.node(["Retailer"], {name: "Reliance mart", visualId: apoc.create.uuid()}) YIELD node as retailer2
CALL apoc.create.node(["Retailer"], {name: "Mart-3", visualId: apoc.create.uuid()}) YIELD node as retailer3
CALL apoc.create.relationship(wholesaler, "LINKED_TO", {}, retailer1)
YIELD rel as rel1
CALL apoc.create.relationship(wholesaler, "LINKED_TO", {}, retailer2)
YIELD rel as rel2
CALL apoc.create.relationship(wholesaler, "LINKED_TO", {}, retailer3)
YIELD rel as rel3
return ""

[glilienfield]

Your original query starts by finding a single retailer, then finds is related wholesaler. Do you want to delete all the other retailers attached to the wholesaler, except the initial retailer found? Do you also want to label the wholesaler as a retailer if there where no retailers to delete?

[Amit_Yadav]

@glilienfield Yes, it could be dynamic, either user can send one or multiple retailers id's to delete it. If the wholesaler doesn't contain any retailer node then it should be labeled as "Retailer" along with "Wholesaler" label after deleting the retailer nodes.

[glilienfield]

So, the input to your query is a list of Retailer visualId values to delete, and you want to delete each one of them?  Do you also want the Wholesaler node attached to a retailer to delete to be labeled Retailer if the wholesaler does not have any retailers left after deleting them?

[Amit_Yadav]

@glilienfield  Yes that is correct behaviour.

[glilienfield]

Forgot to ask, can I assume all the retailer ids are for the same wholesaler, or they could be from different wholesalers? 

[Amit_Yadav]

@glilienfield No reltailer ids can be from different wholesalers too.

[glilienfield · Accepted solution]

I think this works.

WITH ["19e05d50-58e7-4999-b063-d2611677835f", "d3ed7860-1f27-4af4-8979-e2b4b767f3cb", "1c760020-3bb0-45ce-b481-d1f2ad5da397", "dfa1720e-aec7-49f2-91df-a697c7266b22", "15e42476-472d-436d-8f58-755029f5d1c5"] as retailersToDelete
MATCH (r:Retailer)
WHERE r.visualId IN retailersToDelete
MATCH (r)<-[:LINKED_TO]-(ws:Wholesaler)
WITH ws, collect(r) as retailersToDelete
WITH size((ws)-[:LINKED_TO]->(:Retailer)) as totalRetailers, ws, retailersToDelete
call {
    with totalRetailers, ws, retailersToDelete
    with totalRetailers, ws, retailersToDelete
    where totalRetailers = size(retailersToDelete)
    SET ws:Retailer
}
forEach(i in retailersToDelete | detach delete i)

[Amit_Yadav]

@glilienfield Thank You!!, It's worked as expected.