CODEWITHSUNDEEP
neo4jcyphersql-order-byaggregate-functions
assaf_miz84
assaf_miz84

Reputation: 687

Cypher order by aggregation

I need to aggregate data during the query and then order by this data.

According to cypher documentation:

If you want to use aggregations to sort your result set, the aggregation must be included in the RETURN to be used in your ORDER BY.

I have the following cypher query:

START profile=node(31) MATCH (profile)-[r:ROLE]->(story)
WHERE r.role="LEADER" and story.status="PRIVATE" 
WITH story MATCH (story)<-[r?:RATED]-() 
RETURN distinct story ,sum(r.rate) as rate ORDER BY rate DESCENDING

The above query works fine, the thing is I must include sum(r.rate) in my result set.

I am using Cypherdsl via repository ( my repository extends CypherDslRepository ) when quering the response should be story list/page...

Can I use order by aggregation function without including it in the result set?
Any workaround for that?

Thanks.

Upvotes: 1

Views: 953

Answers (1)

Michael Hunger
Michael Hunger

Reputation: 41706

You can do it with an intermediate `WITH``

START profile=node(31) MATCH (profile)-[r:ROLE]->(story)
WHERE r.role="LEADER" and story.status="PRIVATE" 
WITH story 
MATCH (story)<-[r?:RATED]-() 
WITH story ,sum(r.rate) as rate 
ORDER BY rate DESCENDING
RETURN story

And leave off DISTINCT if you already have aggregation.

And optional relationships are slow, so if you run into an perf issue rather use a path expression and get the rel from there.

START profile=node(31) MATCH (profile)-[r:ROLE]->(story)
WHERE r.role="LEADER" and story.status="PRIVATE" 
with story, extract(p in (story)<-[r?:RATED]-() : head(rels(p)) as rated
WITH story , reduce(sum = 0, r in rated : sum + r.rate)  as rate 
ORDER BY rate DESCENDING
RETURN story

Upvotes: 5

Related Questions