Cypher query with two OPTIONAL MATCH and two separate WITH statements

Question

I'm trying to write a single create/update query in Cypher that in addition to normal MATCH to find a "starting node", has two OPTIONAL MATCH queries that searches for two types of relationships from that node that might exist (and nodes they point to). Based on these, I want to be able to do some operations using WITH (and WHERE to exclude NULLs). This worked perfectly, when there was only one OPTIONAL MATCH and one WITH, but the visibility of variables from the OPTIONAL MATCH is not possible in the second WITH (I assume, the second WITH runs in context of the first one).

So, how would I do that, lets go with the following example:

MATCH (node{id:123})
OPTIONAL MATCH p1=(node)-[:TYPE_ONE]->()
OPTIONAL MATCH p2=(node)-[:TYPE_TWO]->()
CREATE (newNode{id=234])-[:TYPE_THREE]->(node)
WITH last(relationships(p1)) as relOne, last(nodes(p1)) as nodeOne, newNode as createdNode
WHERE nodeOne IS NOT NULL
CREATE (createdNode)-[:TYPE_ONE{value:relOne.value}]->(nodeOne)
WITH last(relationships(p2)) as relTwo, last(nodes(p2)) as nodeTwo, newNode as createdNode
WHERE nodeTwo IS NOT NULL
CREATE (createdNode)-[:TYPE_TWO{value:relTwo.value}]->(nodeTwo)

Please note, that either first, second, both or none of the relationships may exists (including multiples).

cybersam cybersam · Accepted Answer · 2015-03-10T15:50:30

Try the following query:

MATCH (node{id:123})
OPTIONAL MATCH p1=(node)-[:TYPE_ONE]->()
OPTIONAL MATCH p2=(node)-[:TYPE_TWO]->()
CREATE (newNode{id:234})-[:TYPE_THREE]->(node)
WITH last(relationships(p1)) as relOne, last(nodes(p1)) as nodeOne, newNode, p2
WHERE nodeOne IS NOT NULL
CREATE (newNode)-[:TYPE_ONE{value:relOne.value}]->(nodeOne)
WITH last(relationships(p2)) as relTwo, last(nodes(p2)) as nodeTwo, newNode
WHERE nodeTwo IS NOT NULL
CREATE (newNode)-[:TYPE_TWO{value:relTwo.value}]->(nodeTwo);

Explanation of differences from your query:

Fixed some typos: id=234] was changed to id:123}.
Removed unnecessary renaming of newNode.
My query adds p2 to the first WITH clause, which is the only way to make it available to the following clauses.

Cypher query with two OPTIONAL MATCH and two separate WITH statements

1 Answers