Neo4j count based on single relationship to node

0
votes

I would like to find all persons who participated in all specified movies, for example in 2 movies: "The Terminator", "True Lies"

I have the following query:

MATCH (t:Title)-[:ACTS_IN]-(p:Principal)-[:ACTS_BY]->(n:Name) 
WHERE t.originalTitle IN ["The Terminator", "True Lies"] 
WITH n, collect(n) as names 
WHERE SIZE(names) >= 2 
RETURN n.primaryName

which works fine if every person participated (:ACTS_BY relationship) only once in every movie. But according to my database schema design, every person can have 0-N :ACTS_BY relationships between Principal and Name nodes(for example the same person can be producer and actor of the movie at the same time).

The issue is that the mentioned Cypher query will also return the person(Name node) in case that person participated 2+ times in one movie and 0 times in another but I only need to return the Name node in case the person participated in each movie.

Please help to improve the query in order to achieve it.

1
neo4jcypher

1 Answers

1
votes

To fix this, you'll want to get distinct values of t, p, n to weed out the duplicates, and only then do a count:

MATCH (t:Title)-[:ACTS_IN]-(p:Principal)-[:ACTS_BY]->(n:Name) 
WHERE t.originalTitle IN ["The Terminator", "True Lies"] 
WITH DISTINCT t, p, n
WITH n, count(n) as occurrences 
WHERE occurrences >= 2 
RETURN n.primaryName