I had an issue while working on the Intermediate Cypher Queries course. I was supposed to find the movie in the db with the highest number of actors and tried the following Cypher code:
MATCH (m:Movie)
RETURN m.title,
[(p:Person)-[:ACTED_IN]->(m) | p.name] AS actors
ORDER BY size(actors) DESC
LIMIT 1
I was able to get the right answer by changing the format of my query but I don't understand why the above query didn't return the right answer. The structure is very similar to a query from one of the videos. From what I can tell, the lists formed are incomplete and not all actors are being included in the list.
I don't think there was anything wrong w/ my Cypher query (possibly the ORDER BY clause). Instead, the Cypher code they were looking for is something like:
MATCH (p:Person)-[:ACTED_IN]->(m:Movie)
RETURN m.title,
collect (p.name) AS actors
ORDER BY size(collect(p.name)) DESC LIMIT 1
Which gives the 'correct' answer of Hamlet (with 24 actors). However, there are 6 movies in the db named Hamlet (all with distinct uniqueness constraints), each having 4 actors in relationships. The Cypher query above is lumping the actors from all 6 movies together...
Yes, and your original query doesn’t deal with that nuance, as the list created using list comprehension to count the actors is only counting for one specific movie node. That query will produce a row of four actors for each Hamlet movie.