cancel
Showing results for 
Search instead for 
Did you mean: 

Head's Up! These forums are read-only. All users and content have migrated. Please join us at community.neo4j.com.

Retrieve Top n nodes/rows by adding property values of a relationship between two nodes

Hi ,
Here's my schema ,
(:Author)-[:WROTE{authorshipWeight}]- >(p:Paper)-[:IS_ABOUT]- >(FieldOfStudy)
I need to retrieve top 25 prominent authors corresponding to each fieldofstudy.Here "Prominent" means I want to add the values of property(authorshipWeight) of the relation :WROTE for each field of study and give me the authors with highest score().
Let's say there are 100 papers in a field of study give me the authors who made more contribution for those papers(sum of authorshipWeight).I have tried the below queru but I don't think so this gives me expected output.
MATCH (a:Author)-[rel:WROTE]- >(p:Papers)-[:IS_ABOUT]- >(fos:FieldOfStudy)
WHERE rel.authorshipWeight is not null RETURN a,fos,p,SUM(toInt(rel.authorshipWeight)) ORDER BY SUM(toInt(rel.authorshipWeight)) desc LIMIT 500

4 REPLIES 4

shan
Graph Buddy

You don't need papers in the result:

MATCH (a:Author)-[rel:WROTE]- >(p:Papers)-[:IS_ABOUT]- >(fos:FieldOfStudy)
WHERE rel.authorshipWeight is not null 
WITH fos, a, SUM(toInt(rel.authorshipWeight)) AS weight ORDER BY weight DESC LIMIT 500
RETURN *

Thank you so much Syed , this works.
Now , Can I add a DISTINCT clause for field of study so that I get only top author for each field of study

You're welcome.
There are multiple ways to do this. Here's an example

MATCH (a:Author)-[rel:WROTE]- >(p:Papers)-[:IS_ABOUT]- >(fos:FieldOfStudy)
WHERE rel.authorshipWeight is not null 
WITH fos, a, SUM(toInt(rel.authorshipWeight)) AS weight ORDER BY weight DESC 
WITH fos, COLLECT([a, weight]) AS allAuthors
RETURN fos, head(allAuthors)[0] AS topAuthor, head(allAuthors)[1] AS weight

Note there is an aggregation function in APOC procedures that can help for cases like this:

MATCH (a:Author)-[rel:WROTE]- >(p:Papers)-[:IS_ABOUT]- >(fos:FieldOfStudy)
WHERE rel.authorshipWeight is not null 
WITH fos, a, SUM(toInteger(rel.authorshipWeight)) AS weight
WITH fos, apoc.agg.maxItems(a, weight) as max
RETURN fos, max.items[0] AS topAuthor, max.value AS weight

Note that max.items will give you a list of authors with the same max value, so if you have a tie, you will have a list of the tying authors.

You can use CALL apoc.help('maxItems') from the browser to see documentation on the function.