query to find all matches within a list

Question

For the life of me i can't seem to build a query for the following scenario.

Given a recipe nodes which has relationships with ingredient nodes. Find another recipe that has all the same ingredients.

Any pointers in the right direction are greatly appreciated.

this does not work of course but was an example of a failed attempt

match (r:Recipie {name:"sweetcake"})-[:HAS_INGREDIENT]-(i:Ingredient)
with  collect(distinct i.name) as Ingredients 
match (r2:Recipie)-[:HAS_INGREDIENT]-(i2:Ingredient) 
where (Ingredients IN colect(distinct i2.name)) 
return r2 limit 5

switching it a bit to

with  collect(distinct i) as Ingredients
....
where (i2 IN Ingredients ) 
...

comes close but them i get recipes that have one ingredient of the original sweetcake node.

Nicole White Nicole White · Accepted Answer · 2014-10-24T13:23:38

I've put together a sample dataset to showcase this to make sure I understand your question. I believe you are wanting to get recipes that have at least all the same ingredients, meaning that the other recipe must have all the same ingredients as the Sweet Cake recipe but might have more ingredients as well.

CREATE (sweetcake:Recipe {name:'Sweet Cake'}),
       (chocolatecake:Recipe {name:'Chocolate Cake'}),
       (lemoncake:Recipe {name:'Lemon Cake'}),

       (sugar:Ingredient {name:'Sugar'}),
       (something:Ingredient {name:'Something Nice'}),
       (milk:Ingredient {name:'Milk'}),
       (chocolate:Ingredient {name:'Chocolate'}),
       (lemon:Ingredient {name:'Lemon'}),

       (sweetcake)-[:HAS_INGREDIENT]->(sugar),
       (sweetcake)-[:HAS_INGREDIENT]->(something),
       (sweetcake)-[:HAS_INGREDIENT]->(milk),

       (chocolatecake)-[:HAS_INGREDIENT]->(sugar),
       (chocolatecake)-[:HAS_INGREDIENT]->(something),
       (chocolatecake)-[:HAS_INGREDIENT]->(milk),
       (chocolatecake)-[:HAS_INGREDIENT]->(chocolate),

       (lemoncake)-[:HAS_INGREDIENT]->(sugar),
       (lemoncake)-[:HAS_INGREDIENT]->(milk),
       (lemoncake)-[:HAS_INGREDIENT]->(lemon)

recipes

I am pretty sure you want Chocolate Cake to be returned, because it shares all of Sugar, Something Nice, and Milk with Sweet Cake. Lemon Cake shouldn't be returned, because it doesn't have Something Nice. So, use ALL() to check that a recipe has at least all the same ingredients as the Sweet Cake recipe.

MATCH (:Recipe {name:'Sweet Cake'})-[:HAS_INGREDIENT]->(i:Ingredient)
WITH COLLECT(i) AS sweetcake_ingredients
MATCH (r:Recipe)-[:HAS_INGREDIENT]->(i:Ingredient)
WHERE r.name <> 'Sweet Cake'
WITH sweetcake_ingredients, r, COLLECT(i) AS other_ingredients
WHERE ALL(x IN sweetcake_ingredients WHERE x IN other_ingredients)
RETURN r.name

And only Chocolate Cake is returned.

query to find all matches within a list

1 Answers