CODEWITHSUNDEEP
neo4jcypherwith-statement
Zack
Zack

Reputation: 551

Cypher query results unexpectedely affected by "WITH" statement

As part of a larger query, I am trying to select PRODUCTs that have a relationship to more than one SKU. I subsequently want to delete these relationships and perform other modifications not included here for the sake of simplicity.

I was surprised to find that while the following query returns a single node:

MATCH (p:PRODUCT)-[p_s_rel:PRODUCT_SKU]->(s:SKU)
WITH s, p, count(s) AS sCount
WHERE sCount> 1 AND id(s) IN [9220, 9201]
RETURN s

adding the relationships p_s_rel in the WITH clause changes the result and returns no nodes:

MATCH (p:PRODUCT)-[p_s_rel:PRODUCT_SKU]->(s:SKU)
WITH s, p, p_s_rel, count(s) AS sCount
WHERE sCount> 1 AND id(s) IN [9220, 9201]
RETURN s

Based on the documentation for WITH, I didn't expect this behavior. Is it not possible to specify relationship identifiers in the WITH clause?

If that's the case, how can I delete the p_s_rel relationships in my example?

Edit: This is on version 2.1.5

Upvotes: 1

Views: 142

Answers (1)

JohnMark13
JohnMark13

Reputation: 3739

Are you sure that the first query is doing what you think that it is? In your query if sCount > 1 then I think that means you have more than one relationship between p and some SKU (i.e multiple relationships between the same nodes). If your WITH statement were WITH p, count(s) AS sCount then you would be matching a single Product with multiple SKUs.

By executing WITH s, p, count(s) AS sCount you are saying carry the currently matched SKU, the currently matched PRODUCT and the count of PRODUCT_SKU relationships between them, whereas WITH p, count(s) AS sCount would be taken to mean carry the currently matched PRODUCT and the count of all PRODUCT_SKU relationships that it has.

In your second query, by including p_s_rel in your WITH clause you will be propagating only a single row with each result - as there will only ever be one distinct p_s_rel to carry forward in each match (a relationship only has one start node, and one end node). This means that sCount will never be greater than 1 - hence your empty resultset. i.e you are saying carry the currently matched SKU, the currently matched PRODUCT, the current realtionship between those two nodes and the count of the end nodes on that relationship (1).

If you want to carry the relationships forward you could use COLLECT, or, as you are going to be restricting your resultset by SKU, maybe you would be better off matching those first:

MATCH (s1:SKU), (s2:SKU)
WHERE ID(s1) = 9220 AND ID(s2) = 9201
WITH s1, s2
MATCH (s1)<-[r1:PRODUCT_SKU]-(p:PRODUCT)-[r2:PRODUCT_SKU]->(s2)
DELETE r1, r2,
RETURN p

Upvotes: 2

Related Questions