CODEWITHSUNDEEP
neo4jcypher
John
John

Reputation: 10019

Trouble using OPTIONAL MATCH with a MATCH and WHERE

I have a cypher query that is not behaving as expected and I'm trying to figure out why. I suspect I don't fully understand how OPTIONAL MATCH works.

The database has one (:'Person::Current') node and one (:'Trait::Current') node. It does not have a (:'PersonTrait::Current') node.

If I run this query, it correctly returns a count(t) of 1

MATCH (n:`Person::Current` {uuid: $person_id}), (t:`Trait::Current` {uuid: $trait_id})
WHERE NOT (
  (n)-[:PERSON_TRAIT]->(:`PersonTrait::Current` {has: true})-[:PERSON_TRAIT]->(t) OR
  (n)-[:PERSON_TRAIT]->(:`PersonTrait::Current` {has: false})-[:PERSON_TRAIT]->(t) OR
  (t)-[:GIVES_TRAIT]->(:`GivesTrait::Current`)-[:GIVES_TRAIT]->(:`Trait::Current`)<-[:PERSON_TRAIT]-(:`PersonTrait::Current` {has: false})<-[:PERSON_TRAIT]-(n)
)
RETURN count(t) as res

When a (:'PersonTrait::Current') node is added to the database in the form

(:`Person::Current`)-[:PERSON_TRAIT]->(:`PersonTrait::Current` {has: true})-[:PERSON_TRAIT]->(:`Trait::Current`)

My query correctly returns a count(t) of 0.

However, if I try and DRY up the query by making use of OPTIONAL MATCH, like so

MATCH (n:`Person::Current` {uuid: $person_id}), (t:`Trait::Current` {uuid: $trait_id})
OPTIONAL MATCH (pt:`PersonTrait::Current`)
WHERE NOT (
  ((n)-[:PERSON_TRAIT]->(pt)-[:PERSON_TRAIT]->(t) AND exists(pt.has)) OR
  (t)-[:GIVES_TRAIT]->(:`GivesTrait::Current`)-[:GIVES_TRAIT]->(:`Trait::Current`)<-[:PERSON_TRAIT]-(pt {has: false})<-[:PERSON_TRAIT]-(n)
)
RETURN count(t) as res

Then the query incorrectly returns a count(t) of 1 when a (:'PersonTrait::Current') node is added to the database in the form

(:`Person::Current`)-[:PERSON_TRAIT]->(:`PersonTrait::Current` {has: true})-[:PERSON_TRAIT]->(:`Trait::Current`)

Anyone know what's going wrong? The WHERE NOT clause should be filtering out (t) nodes if a (pt) node is present with the appropriate pattern.

THANKS!!!

Upvotes: 0

Views: 672

Answers (1)

InverseFalcon
InverseFalcon

Reputation: 30397

I think the issue is understanding the WHERE clause, in that WHERE only applies to the previous MATCH, OPTIONAL MATCH, or WITH clause.

In this case, it's paired with the OPTIONAL MATCH, so rows won't be filtered out when the WHERE is false, it will behave the same as if the OPTIONAL MATCH failed, so newly introduced variables in the OPTIONAL MATCH would be set to null.

If you want the WHERE to filter out rows, pair it with a WITH clause instead:

MATCH (n:`Person::Current` {uuid: $person_id}), (t:`Trait::Current` {uuid: $trait_id})
OPTIONAL MATCH (pt:`PersonTrait::Current`)
WITH n, t, pt
WHERE NOT (
  ((n)-[:PERSON_TRAIT]->(pt)-[:PERSON_TRAIT]->(t) AND exists(pt.has)) OR
  (t)-[:GIVES_TRAIT]->(:`GivesTrait::Current`)-[:GIVES_TRAIT]->(:`Trait::Current`)<-[:PERSON_TRAIT]-(pt {has: false})<-[:PERSON_TRAIT]-(n)
)
RETURN count(t) as res

Upvotes: 4

Related Questions