CODEWITHSUNDEEP
neo4jcyphergraph-databases
Robert Saunders
Robert Saunders

Reputation: 403

Cypher (Neo4j) - Find all relationships as long as one relationship from node satisfies a condition regardless of search path?

Excuse the bad title, I'm a beginner with Cypher and Graph databases in general. I'm not sure if the title fully captures what I am trying to ask, please let me know if you have any better titles!

I have a very simple graph setup with User nodes and Movie nodes and there exists a relationship from a User to a Movie called :REVIEWED that has a rating property that carries the users rating (1.0-5.0 inclusive). See the diagram below:

enter image description here

I think this design makes sense for a movie system for capturing user reviews. I don't think that reviews should exist as their own nodes because they are better represented as a relationship between the user (reviewer) and the movie in a graph. Not to mention the entire purpose properties can exist in relationships are to express scale/weight/metadata in a relationship and this is a great use case for them. However, due to this design I have been having a hard time coming up with a Cypher query to do the following:

So that is, we want to sort the movies based on their average rating however at least one review must be less than a score of 3.0. The query I used to sort the movies based on their average rating is: 

MATCH (movie:Movie)<-[review:REVIEWED]-(user:User) 
RETURN movie.movieTitle, avg(review.rating) as avgRating 
ORDER BY avgRating DESC 
LIMIT 10

This makes sense to me, however when I try to limit the path to reviews with a rating less than 3, see below: 

MATCH (movie:Movie)<-[review:REVIEWED]-(:User) 
WHERE review.rating < 3
RETURN movie.movieTitle, avg(review.rating) as avgRating 
ORDER BY avgRating DESC 
LIMIT 10

Only the paths that have relationships with a rating less than 3 get matched, which is what I should get. However, the issue is when we average the ratings it's only averaging the ratings less than 3.0.

Ideally we want to have all the reviews for that movie as long as there exists a review for that movie with a rating less than 3.0 regardless of whether it is in the matched path. This is where I am getting confused. Because Cypher uses patterns to match paths in the graph how can we use it to check all paths from a node and see if a condition is satisfied and then continue to match all paths based on that result.

Looking forward to hearing what you guys think, thanks in advance!

Upvotes: 0

Views: 620

Answers (1)

mastisa
mastisa

Reputation: 2073

You need a two section query first match movies that have review score undere 3, then average their ratings,

MATCH (movie:Movie)<-[review:REVIEWED]-(:User) 
WHERE review.rating < 3
WITH DISTINCT movie
MATCH (movie)<-[review:REVIEWED]-(:User)
RETURN avg(review.rating) as avgRating 
ORDER BY avgRating DESC 
LIMIT 10

Upvotes: 1

Related Questions