cancel
Showing results forΒ
Did you mean:Β

## How can I return only one matching node to calculate an average value?

Graph Buddy

Hi I don't even know how to properly describe this, apologies for the terrible title:

I have Users who write Posts which can have Ratings and those Ratings are tied to Movies. I'd like to calculate the average ratings, but currently a single user is counting multiple times. Is it possible to find the most recent post by a specific user that has a rating and ONLY use that one in the calculation, but do that for every user who has a post with a rating for that movie? Essentially I'd like to take all users' most recent ratings and average those.

I currently have this:

``````MATCH (Movie { name: "The Dark Knight"})-[:HAS_RATING]-(r:Rating)-[]-(p:Post)-[]-(u:User)
WITH DISTINCT u, avg(r.value) AS ratingAverage, r
RETURN apoc.math.round(ratingAverage, 2)
``````

5 REPLIES 5
Graph Fellow

Hi @geronimo4j!

Two things...

1. Have you consider using the rating as a property of a relation between Post and Movie?

2. I guess there's a property with date of the Post creation. What's the name of it?

Bennu

Graph Buddy

Hi @Bennu ! That's an interesting idea for the rating as a property of a relationship rather than a node itself, what might that help with?

And there's a property for `created_date` on post that just has a DateTime.

Thank you!

Dan

Graph Fellow

Hi @geronimo4j !

Having a 4x4 without never using the front traction it's kind of a waste, dont you agree?

``````MATCH (m:Movie { name: 'The Dark Knight'})-[:HAS_RATING]-(r:Rating)-[]-(p:Post)-[]-(u:User)
WITH m, apoc.agg.maxItems(r,p.created_date).items[0].value as r, u
RETURN m as movie, apoc.math.round(avg(r), 2) as averageRate
``````

I'm assuming you can use APOC, otherwise it'll be almost esthetically impossible without a new model.

Bennu

Graph Buddy

Thanks @Bennu ! That's a great analogy, makes me feel like I'm not using enough of what Neo4j has to offer!

Just to ensure I'm getting it right, you would make the rating a property between Post and Movie, but then how would you call to get that rating when calling the posts, and then the most recent rating when calculating the average ratings for movies?

Graph Fellow

Hi @geronimo4j
Well, there're couple things to consider. What other properties you have on Rating besides value. What about Post? Does every Post has a Rating?

Query may not be two different (besides saving some APOC) and disk space. Have you checked if the query previously shared works?

Bennu

Nodes 2022

NODES 2022, Neo4j Online Education Summit

All the sessions of the conference are now available online

Neo4j Resources