CODEWITHSUNDEEP
neo4jcypher
Richard
Richard

Reputation: 582

Possible to combine these two queries

I have two cypher queries and would like to know if there is a possibility to get the Points and HeadHunter information within one single statement.

MATCH (s:SEASON)-[*]->(e:EVENT)<-[f:FINISHED]-(p:PLAYER)
    WHERE s.id = 8 AND e.id <= 1197
    RETURN p, sum(f.points) AS Points
    ORDER BY Points DESC

MATCH (s:SEASON)-[*]->(e:EVENT)<-[el:ELIMINATED]-(p:PLAYER)
    WHERE s.id = 8 AND e.id <= 1197
    RETURN p, sum(el.points) AS HeadHunter
    ORDER BY HeadHunter DESC

I played around with different approaches but none of them worked. I would like to do something like this:

MATCH (s:SEASON)-[*]->(e:EVENT)<-[el:ELIMINATED]-(p:PLAYER),(e)<-[f:FINISHED]-(p)
    WHERE s.id = 8 AND e.id <= 1197
    RETURN p, sum(el.points) AS HeadHunter, sum(f.points) AS Points

Upvotes: 1

Views: 60

Answers (2)

cybersam
cybersam

Reputation: 67044

Does this work for you?

MATCH (s:SEASON)-[*]->(e:EVENT)<-[rel:FINISHED|ELIMINATED]-(p:PLAYER)
WHERE s.id = 8 AND e.id <= 1197
WITH p, COLLECT(rel) AS rels
RETURN p,
  REDUCE(s = 0, x IN rels | CASE WHEN TYPE(x) = 'FINISHED' THEN s + x.points ELSE s END) AS Points,
  REDUCE(s = 0, x IN rels | CASE WHEN TYPE(x) = 'ELIMINATED' THEN s + x.points ELSE s END) AS HeadHunter

This should return the relevant sums for each PLAYER that either finished and/or got eliminated.

Upvotes: 3

FrobberOfBits
FrobberOfBits

Reputation: 18022

In principle I think your query can work, but you may have made a mistake with your p variable. In this query:

MATCH (s:SEASON)-[*]->(e:EVENT)<-[el:ELIMINATED]-(p:PLAYER),(e)<-[f:FINISHED]-(p)
    WHERE s.id = 8 AND e.id <= 1197
    RETURN p, sum(el.points) AS HeadHunter, sum(f.points) AS Points

Note that p has to be a player with both the ELIMINATED relationship and the FINISHED relationship. I'm guessing based on the semantics of your domain, that doesn't make sense; you can't be eliminated and have finished, right?

So you might want to try some variant of this:

MATCH (s:SEASON)-[*]->(e:EVENT)<-[el:ELIMINATED]-(pElim:PLAYER),(e)<-[f:FINISHED]-(pFinish)
    WHERE s.id = 8 AND e.id <= 1197
    RETURN pElim, sum(el.points) AS HeadHunter, pFinish, sum(f.points) AS Points

Note with this you'll have a lot of extra rows of output for various combinations of pElim and pFinish but the respective values should be correct.

Upvotes: 1

Related Questions