Return actors and movies, which where not directed by a specific director, but have worked together with this director in other movies. Cypher (neo4j)

Question

For example:

The query below will return the 10 actors with the most co-operations with Lana Wachowski.

MATCH (actor:Person)-[:ACTED_IN]->(m:Movie)<-[:DIRECTED]-(lana:Person {name:  'Lana Wachowski'})
RETURN actor.name , COUNT(m), collect(m.title)
ORDER by COUNT(m) desc
LIMIT 10

However I want a query which will return the movies the above actors :ACTED_IN, which were not directed by Lana Wachoswki,

I did something like this, but eventually will return me all the actors who had a co-operation with this director and not the top 10 in an order.

MATCH (actor:Person)-[:ACTED_IN]->(m:Movie)<-[:DIRECTED]-(lana:Person {name:  'Lana Wachowski'}),
(actor:Person)-[:ACTED_IN]->(other:Movie)
WITH actor, other
WHERE NOT EXISTS((lana)-[:DIRECTED]->(other:Movie)<-[:ACTED_IN]-(actor))
RETURN actor.name , other.title

Answer 1

Eventually seems, just by passing the m variable, then it can be used again to sort the RETURN.

MATCH (actor:Person)-[:ACTED_IN]->(m:Movie)<-[:DIRECTED]-(lana:Person {name:  'Lana Wachowski'}),
(actor:Person)-[:ACTED_IN]->(other:Movie)
WITH actor, m, other
WHERE NOT EXISTS((lana)-[:DIRECTED]->(other:Movie)<-[:ACTED_IN]-(actor))
RETURN actor.name , other.title, COUNT(m)
ORDER BY COUNT(m) desc
LIMIT 10

HOWEVER if i try to run the query below I have duplicates in the collection lists.

MATCH (actor:Person)-[:ACTED_IN]->(m:Movie)<-[:DIRECTED]-(lana:Person {name:  'Lana Wachowski'}),
(actor:Person)-[:ACTED_IN]->(other:Movie)
WITH actor, m, other
WHERE NOT EXISTS((lana)-[:DIRECTED]->(other:Movie)<-[:ACTED_IN]-(actor))
RETURN DISTINCT actor.name , collect(other.title), COUNT(m)
ORDER BY COUNT(m) desc
LIMIT 10

Answer 2

You can collect the actors that acted in a movie directed by Lana. Then for each actor, get the movies and collect the directors. Check that the set of directors does not have lana in it. Then collect the titles of these movies and sort by most number of movies.

So all actors who were directed by Lana and has other movies not directed by Lana are Tom Hanks (Cloud Atlas), Keanu (Matrix), Ben Miles (Speed Racer), Hugo (Cloud Atlas and Matrix) and Rain (Speed Racer)

WITH [(a:Person)-[:ACTED_IN]->(:Movie)<-[:DIRECTED]-(:Person {name: 'Lana Wachowski'})|a] as actors
UNWIND actors as actor
MATCH (actor)-[:ACTED_IN]->(m:Movie)<-[:DIRECTED]-(p:Person)
WITH actor, m, collect(p) as directors WHERE NOT ANY (d in directors WHERE d.name = 'Lana Wachowski')
RETURN actor.name , collect(m.title), COUNT(m)
ORDER BY COUNT(m) desc
LIMIT 10

REsult:

╒══════════════╤══════════════════════════════════════════════════════════════════════╤══════════╕
│"actor.name"  │"collect(m.title)"                                                    │"COUNT(m)"│
╞══════════════╪══════════════════════════════════════════════════════════════════════╪══════════╡
│"Tom Hanks"   │["Apollo 13","You've Got Mail","A League of Their Own","Joe Versus the│11        │
│              │ Volcano","That Thing You Do","The Da Vinci Code","Cast Away","The Gre│          │
│              │en Mile","Sleepless in Seattle","The Polar Express","Charlie Wilson's │          │
│              │War"]                                                                 │          │
├──────────────┼──────────────────────────────────────────────────────────────────────┼──────────┤
│"Keanu Reeves"│["Something's Gotta Give","The Replacements","Johnny Mnemonic","The De│4         │
│              │vil's Advocate"]                                                      │          │
├──────────────┼──────────────────────────────────────────────────────────────────────┼──────────┤
│"Ben Miles"   │["Ninja Assassin","V for Vendetta"]                                   │2         │
├──────────────┼──────────────────────────────────────────────────────────────────────┼──────────┤
│"Hugo Weaving"│["V for Vendetta"]                                                    │1         │
├──────────────┼──────────────────────────────────────────────────────────────────────┼──────────┤
│"Rain"        │["Ninja Assassin"]                                                    │1         │
└──────────────┴──────────────────────────────────────────────────────────────────────┴──────────┘