# WITH WHERE OPTIONAL problem

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 * ```

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 *

1 Like

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 *
``````
1 Like

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 * ```