CODEWITHSUNDEEP
neo4jcypher
Pierre
Pierre

Reputation: 943

OPTIONAL MATCH returns no path for disconnect nodes

I find weird that using OPTIONAL MATCH nodes that don’t have the expected relationship are not returned as a single node in path.

OPTIONAL MATCH path = (:Person) -[:LIKES]- (:Movie) 
UNWIND nodes(p) as n 
UNWIND rels(p) as e 
WITH n 
WHERE HEAD(LABELS(n)) = “Person” 
return COUNT(DISTINCT n)

The number of people returned only includes those who liked a movie. By using OPTIONAL I would have expected all people to be returned. Is there a workaround to this or am I doing some this wrong in the query?

Upvotes: 0

Views: 168

Answers (1)

InverseFalcon
InverseFalcon

Reputation: 30407

A better way to go about this would be to match to all :People nodes first, then use the OPTIONAL MATCH to match to movies (or, if you want a collection of the movies they liked, use pattern comprehension).

If you do need to perform an UNWIND on an empty collection without wiping out the row, use a CASE around some condition to use a single-element list rather than the empty list.

MATCH (n:Person)   // match all persons
OPTIONAL MATCH p = (n) -[:LIKES]- (m:Movie) // p and m are the optionals
UNWIND CASE WHEN p is null THEN [null] ELSE nodes(p) END as nodes  // already have n, using a different variable
UNWIND CASE WHEN p is null THEN [null] ELSE rels(p) END as e // forcing a single element list means UNWIND won't wipe out the row
WITH n 
WHERE HEAD(LABELS(n)) = “Person”  // not really needed at all, and bad practice, you don't know the order of the labels on a node
return COUNT(DISTINCT n) // if this is really all you need, just keep the first match and the return of the query (without distinct), don't need anything else

Upvotes: 1

Related Questions