Use vars from before WITH statement in RETURN statement in Neo4j Cypher

Question

I'm starting with Neo4j (v2.1.5) and I'm having an issue with the following Cypher query:

MATCH (actor:Person{name:"Tom Cruise"})-[role:ACTED_IN]->(movies)<-[r:ACTED_IN]-(coactors)
WITH  coactors, count(coactors) as TimesCoacted
RETURN coactors.name, avg(TimesCoacted)
ORDER BY avg(TimesCoacted) DESC

It is based on the mini movie graph which comes with Neo4j installation.

Everything works fine, it shows all coactors which coacted in movies with Tom Cruise and how many times they coacted together, but the problem occurs when I want to list in which movies they coacted. Placing 'movies' variable in RETURN statement is throwing the following error:

movies not defined (line 3, column 9)
"RETURN  movies, coactors.name, avg(TimesCoacted)"
         ^

Is there any way I can do it in one query?

wassgren wassgren · Accepted Answer · 2015-01-12T14:31:27

Try the following:

MATCH 
    (actor:Person{name:"Tom Cruise"})-[role:ACTED_IN]->(movies)<-[r:ACTED_IN]-(coactors)
WITH 
    coactors, 
    count(coactors) as TimesCoacted, 
    movies // You have declare "movies" here in order to use it later in the query
RETURN 
    movies, 
    coactors.name, 
    avg(TimesCoacted)
ORDER BY 
    avg(TimesCoacted) DESC

What you define in the WITH-statement is the only thing that is available for further processing. In the original question the movies were not carried on to the next section (it was not part of WITH) and therefore movies could not be used in the return statement.

Edit: After updates from the OP the following was added.

Another example. If you wish to count the number of times the actors have coacted in a movie and list the movie-titles as well. Try the following:

MATCH 
    (actor:Person {name:"Tom Cruise"})-[:ACTED_IN]->(movie)<-[:ACTED_IN]-(coactor:Person)
WITH
    actor,
    coactor,
    collect (distinct movie.title) as movieTitles
RETURN
    actor.name as actorName, 
    coactor.name as coactorName, 
    movieTitles, 
    size(movieTitles) as numberOfMovies

Use vars from before WITH statement in RETURN statement in Neo4j Cypher

2 Answers