CODEWITHSUNDEEP
neo4jcypher
user3560165
user3560165

Reputation: 33

Issue combining two cypher queries with UNION

I ran into the following problem when combining two cypher queries on console.neo4j.org

The query:

MATCH (p1:Crew)-[r_pfq]->(fq:Crew)
WHERE fq.name IN ["Neo", "Morpheus"]
RETURN distinct(p1) AS person, count(r_pfq) AS friend_score, collect(fq.name) AS friends
ORDER BY friend_score DESC
LIMIT 10

works fine, as does

MATCH (f:Crew)<-[r_fqf]-(fq:Crew)
WHERE fq.name IN ["Neo", "Morpheus"]
WITH distinct(f), count(r_fqf) AS weight
ORDER BY weight DESC
LIMIT 10
MATCH f<--(p:Crew)
RETURN distinct(p) AS person, sum(weight) AS friend_score, collect(f.name) AS friends
ORDER BY friend_score DESC
LIMIT 10

Now when I try to combine the query results using the UNION command, i.e.

MATCH (p1:Crew)-[r_pfq]->(fq:Crew)
WHERE fq.name IN ["Neo", "Morpheus"]
RETURN distinct(p1) AS person, count(r_pfq) AS friend_score, collect(fq.name) AS friends
ORDER BY friend_score DESC
LIMIT 10
UNION
MATCH (f:Crew)<-[r_fqf]-(fq:Crew)
WHERE fq.name IN ["Neo", "Morpheus"]
WITH distinct(f), count(r_fqf) AS weight
ORDER BY weight DESC
LIMIT 10
MATCH f<--(p:Crew)
RETURN distinct(p) AS person, sum(weight) AS friend_score, collect(f.name) AS friends
ORDER BY friend_score DESC
LIMIT 10

I get the error

Error: org.neo4j.graphdb.NotFoundException: Unknown identifier `weight`.

Can anyone provide me with an explanation why these query results can not be combined and how to properly do so? Why is the identifier known when running both queries separately but unknown in a UNION-combined query?

Upvotes: 0

Views: 1373

Answers (1)

cybersam
cybersam

Reputation: 66957

EDIT

The following simpler query is basically equivalent, except that the second query in the UNION does not ORDER BY weight. This is because we are already ordering by the derived friend_score, so it seemed redundant. Also, in order for a variable to be included in the ORDER BY clause, it has to be in the RETURN clause -- but the first query in the UNION does not have a weight variable, which would have violated the requirements for a legal UNION statement.

In addition, there is a second WITH clause in the second query because you have to define the variables used in an ORDER BY clause (like friend_score) before the RETURN clause!

MATCH (p1:Crew)-[r_pfq]->(fq:Crew)
WHERE fq.name IN ["Neo", "Morpheus"]
RETURN DISTINCT (p1) AS person, count(r_pfq) AS friend_score, collect(fq.name) AS friends
ORDER BY friend_score DESC 
LIMIT 10
UNION
MATCH (p:Crew)-->(f:Crew)<-[r_fqf]-(fq:Crew)
WHERE fq.name IN ["Neo", "Morpheus"]
WITH f, count(r_fqf) AS weight, p
WITH f, sum(weight) AS friend_score, p
RETURN DISTINCT (p) AS person, friend_score, collect(DISTINCT (f).name) AS friends
ORDER BY friend_score DESC 
LIMIT 10

Upvotes: 1

Related Questions