CODEWITHSUNDEEP
neo4jcypher
Eric
Eric

Reputation: 21

WHERE NOT clause with additional type() operators

I am attempting to write a query using WHERE NOT with additional type() operators.

Here's a positive match that works properly and returns users who have visited a club during October, 2017.

MATCH (user:User)-[visited]->(club:Club) 
WHERE type(visited) >= "20171001" and type(visited) <= "20171031"
RETURN user;

Here's a WHERE NOT that works properly and returns users who have NOT visited a club.

MATCH (user:User) 
WHERE NOT ( (user:User)-[]->(:Club) ) RETURN user;

But I'm having trouble formatting a query properly that returns users who have NOT visited a club in October, 2017.

MATCH (user:User) 
WHERE NOT ( (user:User)-[visited]->(:Club) 
AND type(visited) >= "20171001" and type(visited) <= "20171031")
RETURN user;

This returns a "visited not defined" error. Which would seem to indicate that it needs to be defined in the initial match, but adding it to the initial match like this returns no records, so I'm stuck.

MATCH (user:User)-[visited]->(:Club) 
WHERE NOT ( (user:User)-[visited]->(:Club) 
AND type(visited) >= "20171001" and type(visited) <= "20171031")
RETURN user;

This evaluates, but returns no results. The issue is that it's looking for the visited relationship, so if a user has never visited a club, they won't match, and therefore, won't be returned as users who didn't visit in October.

Upvotes: 0

Views: 49

Answers (2)

InverseFalcon
InverseFalcon

Reputation: 30397

Here's an alternative using OPTIONAL MATCH for the relationship to exclude, and then keeping only rows where such a relationship doesn't exist.

MATCH (user:User)
OPTIONAL MATCH (user)-[visited]->(:Club)
WHERE type(visited) STARTS WITH "201710"
WITH user
WHERE visited IS NULL
RETURN user

Upvotes: 2

Gabor Szarnyas
Gabor Szarnyas

Reputation: 5047

As you observed, it's not possible to introduce new variables in the WHERE clause:

Note that you cannot introduce new variables here.

I think the best approach is to list users who did visit a club in October, collect them to a list, then get all users and only return those who are not in the list.

MATCH (user:User)-[visited]->(:Club)
WHERE type(visited) >= "20171001" AND type(visited) <= "20171031"
WITH collect(user) AS visitorsInOctober
MATCH (user:User)
WHERE NOT user IN visitorsInOctober
RETURN user

Having said that, I believe your data model should be refined. Relationships can also have properties, so you could use define a relationship with the type VISITED and with a property date. This would result in a query similar to this:

MATCH (user:User)-[visited:VISITED]->(:Club)
WHERE visited.date >= "20171001" AND visited.date <= "20171031"
// ...

Upvotes: 0

Related Questions