I often notice people (and agents) write Cypher that filters one node against another, such as WHERE costar <> actor. Sometimes the filter is needed. Sometimes the pattern has already excluded the node, and the filter does nothing.
If MATCH, WHERE and RETURN are new to you, start with Cypher Fundamentals.
Understanding relationship uniqueness
Knowing when to filter on a node, and when not to, depends on understanding how a pattern follows relationships.
Within a single MATCH clause, each relationship is followed only once. A node can appear in the pattern as many times as you like. A relationship cannot.
For a pattern to end at the node it started from, it must arrive along a different relationship from the one it started along. Whether that relationship exists depends on your data, which is why the filter is sometimes necessary and sometimes redundant.
When an actor has two relationships
Say we have a graph that models people and movies, where each ACTED_IN relationship represents a role that a person played in a movie.
In most cases a person plays one role in a movie, so there is one relationship between them. For a few outliers, an actor plays more than one. Take The Nutty Professor, where Eddie Murphy plays several characters, including Sherman Klump and Buddy Love.
The following query will find the other people and roles from the movie:
MATCH (eddie:Person {name: 'Eddie Murphy'})-[r1:ACTED_IN]->(m:Movie)<-[r2:ACTED_IN]-(costar:Person)
RETURN m.title AS movie, r1.role AS eddieRole, costar.name AS costar, r2.role AS costarRole
ORDER BY costar, eddieRoleThe following graph shows the pattern in the query. Hover over the numbered steps to step through the match one phase at a time:
- 1
Start at Eddie. The pattern anchors on
(eddie:Person {name: 'Eddie Murphy'}). - 2
Follow an
ACTED_INrelationship to the movie. Eddie has two relationships to The Nutty Professor. Say the pattern follows Sherman Klump. That relationship is now used for the rest of thisMATCH. - 3
Follow a different
ACTED_INrelationship to a person. Sherman Klump is off limits. Following Buddy Love reaches Eddie, and following Carla Purty reaches Jada.
The query returns the following rows:
| movie | eddieRole | costar | costarRole |
|---|---|---|---|
| The Nutty Professor | Buddy Love | Eddie Murphy | Sherman Klump |
| The Nutty Professor | Sherman Klump | Eddie Murphy | Buddy Love |
| The Nutty Professor | Buddy Love | Jada Pinkett Smith | Carla Purty |
| The Nutty Professor | Sherman Klump | Jada Pinkett Smith | Carla Purty |
Because each relationship is followed only once, Eddie's two roles pair with each other, once in each order, which makes him his own co-star twice. Jada's single role pairs with each of Eddie's two, which gives her two rows.
Where to WHERE NOT
To exclude Eddie from the results above, and to remove the duplicate rows, use a WHERE clause:
MATCH (eddie:Person {name: 'Eddie Murphy'})-[:ACTED_IN]->(m:Movie)<-[:ACTED_IN]-(costar:Person)
WHERE NOT costar = eddie // (1)
RETURN DISTINCT m.title AS movie, costar.name AS costar // (2)- 1
Removes any row where
costaris the same node aseddie.WHERE costar <> eddieis equivalent - 2
Returns each movie and co-star combination once, so the two rows for Jada become one
If you run this query against the dataset, you will see that Eddie has been removed from the results and that Jada appears once:
| movie | costar |
|---|---|
| The Nutty Professor | Jada Pinkett Smith |
How you model roles is a design decision. Some versions of the movies dataset create one ACTED_IN relationship per role, with a role property, which is the model used above. Others create a single ACTED_IN relationship per actor and movie, with a roles property that holds a list of strings. With the list, an actor never has two relationships to the same movie, so the co-star query never returns the actor. Which model you choose depends on the results you want from your queries.
Where not to WHERE NOT
In some cases the WHERE clause is not needed. A director cannot direct the same movie twice, so a director and a movie have only one DIRECTED relationship between them.
The following query finds the other movies by the director of The Nutty Professor:
MATCH (m:Movie {title: 'The Nutty Professor'})<-[:DIRECTED]-(d:Person)-[:DIRECTED]->(other:Movie)
RETURN d.name AS director, other.title AS movieThe following graph shows which relationship the pattern follows at each step, and why none of them can end at The Nutty Professor:
- 1
Start at the movie. The pattern anchors on
(m:Movie {title: 'The Nutty Professor'}). - 2
Follow
DIRECTEDto the director. Tom Shadyac has exactly oneDIRECTEDrelationship to this movie, and the relationship is now used. - 3
Follow a different
DIRECTEDrelationship. The only unused one reaches Liar Liar, so the pattern cannot end at The Nutty Professor.
The result set does not include The Nutty Professor. Step 2 uses the only DIRECTED relationship between Tom Shadyac and the movie, so step 3 can only follow a relationship to a different movie:
| director | movie |
|---|---|
| Tom Shadyac | Liar Liar |
Swap DIRECTED for ACTED_IN in the same pattern and The Nutty Professor appears in its own list, because Eddie has two ACTED_IN relationships to it.
Including the original movie
Sometimes you want the original movie in the results, for example to list every movie the director has made. The rule applies within a single MATCH clause and starts again with the next one. Split the pattern across two MATCH clauses and the second can follow the relationship that the first one used:
MATCH (m:Movie {title: 'The Nutty Professor'})<-[:DIRECTED]-(d:Person)
MATCH (d)-[:DIRECTED]->(movie:Movie)
RETURN d.name AS director, movie.title AS movie
ORDER BY movie| director | movie |
|---|---|
| Tom Shadyac | Liar Liar |
| Tom Shadyac | The Nutty Professor |
Comma-separated patterns inside one MATCH clause share the rule, so only separate MATCH clauses start again.
The default match mode in Cypher is named DIFFERENT RELATIONSHIPS. The REPEATABLE ELEMENTS match mode opts out of the rule.
Before you add the filter
Before you add the filter, consider how the pattern in your query will be fulfilled. If the model allows several relationships between a pair of nodes, the pattern can reach the same node again by following a different relationship, and you need the WHERE NOT. If it allows one, the pattern has already excluded the node.
Duplicate relationships also come from running CREATE twice. Use MERGE when loading data to keep one relationship per pair.
To practice patterns and filters like these, try the free Cypher Fundamentals course:
Comments (0)
Loading comments...