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

[geronimo4j]

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)

Thank you in advance!

[Bennu]

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

[geronimo4j]

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

[Bennu]

Hi @geronimo4j !

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

Next should work, but refactoring your model will help you a lot on the future.

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

[geronimo4j]

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?

[Bennu]

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