neo4j cypher suggestion based on common relation rating

0
votes

Scenario: graph image
John doe has rated 2 ingredients, 2 of those ingredients happen to belong to a soup recipe, and only 1 to pizza. The query should return the soup recipe because the avg of those ingredient ratings is > 5

What I have: I started with below query:

MATCH (:Subject {ref: 1})-[ir:INGREDIENT_RATING]->(:Ingredient)<-[:HAS_INGREDIENT]-(r:Recipe) WHERE ir.value > 5 return r;

What I would like to happen: This returns recipes where an ingredient has a rating above 5, but this does not take into account that other ingredients of that recipe could have lower ratings given by that user.

So I have to expand on above query but I'm a bit clueless where to start.

Thanks in advance,

Update 1: Based on @InverseFalcon I came up with this, which gives me the results I expect:

MATCH (:Subject {ref: '1'})-[ir:INGREDIENT_RATING]->(i:Ingredient)-[:HAS_INGREDIENT]-(r:Recipe)-[:KITCHEN]->(k:Kitchen)
MATCH (r)-[HAS_INGREDIENT]-(in:Ingredient)
WITH r, k, in, sum(ir.value) AS sum
WHERE sum > 10
RETURN DISTINCT r, collect(DISTINCT in) AS ingredients, k AS kitchen, sum
ORDER BY sum DESC

The second match is because without it, it only returns ingredients with a rating, I need all of them.

There is only one oddity and that is I get a duplicate result even tough I use distinct on r.

1
neo4jcypher
I'm not sure what you're asking. Please provide some examples of data and what you'd expect returned. – betseyb
@betseyb I updated the scenario with an image and a better description. Hopefully it is better to understand what i try to accomplish. – Edwin

1 Answers

0
votes

Sounds like you need the avg() aggregation function to take the average of multiple values. Does this work for you?

MATCH (:Subject {ref: 1})-[ir:INGREDIENT_RATING]->(:Ingredient)<-[:HAS_INGREDIENT]-(r:Recipe) 
WITH r, avg(ir.value) as avg
WHERE avg > 5 
RETURN r;