WITH WHERE OPTIONAL problem

[hagen_ivar]

Hi!

I've got some problems to get the OPTIONAL MATCH to work when using the WITH and WHERE operators.
To simplify my complex query I have created the below example. I would like to return all Cats (a) and all persons (p) for whom all friends are bffs.
The problem is that if there is no such person then I will not get anything in result. I would still like the cat to be returned. I understand why it happens but how do I rewrite the query? Please don't just tell me to swap order of the two queries. 😉 Thanks!

Regards Ivar Hagen (Developer at Volvo Cars, Gothenburg, Sweden).

CREATE (a:Animal {type:'Cat', name:'Garfield'})

CREATE (p1:Person {name:'Abby'})

CREATE (p2:Person {name:'Betty'})

CREATE (p3:Person {name:'Carol'})

CREATE (p1)-[:HAS_FRIEND {type:'bff'}]->(p2)

CREATE (p1)-[:HAS_FRIEND {type:'bff'}]->(p3) // Removing this row and the query will return an empty set []

CREATE (p2)-[:HAS_FRIEND {type:'bff'}]->(p1)

CREATE (p2)-[:HAS_FRIEND]->(p3)

MATCH(a:Animal)
WHERE a.type = "Cat"
WITH COLLECT(a) AS a

OPTIONAL MATCH(p:Person)-[r2:HAS_FRIEND]->(p2:Person)
WITH a, p, COLLECT(p2) AS p2, COUNT(p2) AS friends, SIZE( (p)-[:HAS_FRIEND {type:"bff"}]->(:Person) ) AS bffs
WHERE friends = bffs
WITH a, p, p2
RETURN *

[hagen_ivar]

I kinda managed to solve it myself by using CASE instead of WHERE. 😉

MATCH(a:Animal)
WHERE a.type = "Cat"
WITH COLLECT(a) AS a
OPTIONAL MATCH(p:Person)-[r2:HAS_FRIEND]->(p2:Person)
WITH a, p,
CASE WHEN (COUNT(p2) - SIZE( (p)-[:HAS_FRIEND {type:"bff"}]->(:Person) ) ) <> 0 THEN NULL ELSE COLLECT(p2) END AS x
WITH distinct a, x
RETURN *

[michael_hunger]

You could also do:

MATCH(a:Animal)
WHERE a.type = "Cat"
WITH COLLECT(a) AS a
OPTIONAL MATCH(p:Person)-[r2:HAS_FRIEND]->(p2:Person)
WITH a,p, collect(r2) as rels, collect(p2) as friends
WHERE all(r in rels WHERE r.type = 'bff')
RETURN *

[hagen_ivar · Accepted solution]

Thanks!

The All function is a good solution instead of using COUNT and SIZE, but when there are no persons who has all friends as bffs then it still returns empty. I want it to return the Animals (a) no matter what.

Could the All function be combined with the CASE solution maybe?
This is good enough for me. 🙂

MATCH(a:Animal)

WHERE a.type = "Cat"

WITH COLLECT(a) AS a

OPTIONAL MATCH(p:Person)-[r2:HAS_FRIEND]->(p2:Person)

WITH CASE WHEN all(r in collect(r2) WHERE r.type = 'bff') THEN collect(p2) ELSE [] END AS bffs, a, p

RETURN *