GraphGist: Master of Pancakes: Find Missing Recipe Ingredients

by boggle

Let’s say you have a database of recipes and ingredients, as well as chefs and their current kitchen inventory.

One chef (admired and known only as "The Master of Pancakes" by his colleages) would like to make some pancakes. Before he can start, he needs to know if he has the right ingredients in his kitchen.

We start with by creating ingredients.

MERGE (:Ingredient {name: "Flour"})
MERGE (:Ingredient {name: "Baking Powder"})
MERGE (:Ingredient {name: "Salt"})
MERGE (:Ingredient {name: "Sugar"})
MERGE (:Ingredient {name: "Egg"})
MERGE (:Ingredient {name: "Milk"})
MERGE (:Ingredient {name: "Butter"});

We also describe a good pancake recipe.

MERGE (flour:Ingredient {name: "Flour"})
MERGE (powder:Ingredient {name: "Baking Powder"})
MERGE (salt:Ingredient {name: "Salt"})
MERGE (sugar:Ingredient {name: "Sugar"})
MERGE (egg:Ingredient {name: "Egg"})
MERGE (milk:Ingredient {name: "Milk"})
MERGE (butter:Ingredient {name: "Butter"})
CREATE (pancake:Recipe {title: "Ultimate Pancake Recipe"})
CREATE (pancake)-[:requires_value]->(flour_val:Value)-[:is_part_of]->(flour)
CREATE (pancake)-[:requires_value]->(powder_val:Value)-[:is_part_of]->(powder)
CREATE (pancake)-[:requires_value]->(salt_val:Value {value: "Himalaya"})-[:is_part_of]->(salt)
CREATE (pancake)-[:requires_value]->(sugar_val:Value {value: "White"})-[:is_part_of]->(sugar)
CREATE (pancake)-[:requires_value]->(egg_val:Value {value: "Bio"})-[:is_part_of]->(egg)
CREATE (pancake)-[:requires_value]->(milk_val:Value {value: "Bio"})-[:is_part_of]->(milk)
CREATE (pancake)-[:requires_value]->(butter_val:Value {value: "Plain"})-[:is_part_of]->(butter)
RETURN *;
Loading graph...

Hmm pancakes, yummy!

Next, let’s describe what the Master of Pancakes has left in his kitchen:

CREATE (pancakeMaster:Chef {name: "Master of Pancakes"})
MERGE (flour:Ingredient {name: "Flour"})
MERGE (salt:Ingredient {name: "Salt"})
MERGE (sugar:Ingredient {name: "Sugar"})
MERGE (egg:Ingredient {name: "Egg"})
MERGE (milk:Ingredient {name: "Milk"})
MERGE (butter:Ingredient {name: "Butter"})
CREATE (pancakeMaster)-[:has_value]->(flour_val:Value)-[:is_part_of]->(flour)
CREATE (pancakeMaster)-[:has_value]->(salt_val:Value {value: "Fleur de Sel"})-[:is_part_of]->(salt)
CREATE (pancakeMaster)-[:has_value]->(sugar_val:Value {value: "Brown"})-[:is_part_of]->(sugar)
CREATE (pancakeMaster)-[:has_value]->(egg_val:Value {value: "Bio"})-[:is_part_of]->(egg)
CREATE (pancakeMaster)-[:has_value]->(milk_val:Value {value: "Bio"})-[:is_part_of]->(milk)
CREATE (pancakeMaster)-[:has_value]->(butter_val:Value {value: "Salty"})-[:is_part_of]->(butter)
RETURN *;
Loading graph...

Now, that we have all the data, let’s see if we are ready to make pancakes:

MATCH
  (pancake:Recipe {title: "Ultimate Pancake Recipe"})-[:requires_value]->(req_value:Value)-[:is_part_of]->(ingredient:Ingredient)
OPTIONAL MATCH (ingredient)<-[:is_part_of]-(avail_value)<-[:has_value]-(pancakeMaster:Chef {name: "Master of Pancakes"})
RETURN ingredient.name AS ingredient, req_value, avail_value;
Loading table...

That’s already not bad but we’d like to only see where we are missing ingredients or have the wrong kind. For this we need to compare what is required with what’s available:

MATCH
  (pancake:Recipe {title: "Ultimate Pancake Recipe"})-[:requires_value]->(req_value:Value)-[:is_part_of]->(ingredient:Ingredient)
OPTIONAL MATCH (ingredient)<-[:is_part_of]-(avail_value)<-[:has_value]-(pancakeMaster:Chef {name: "Master of Pancakes"})
WITH ingredient, req_value, avail_value
WHERE avail_value IS NULL OR req_value.value <> avail_value.value
RETURN
  ingredient.name AS ingredient,
  CASE WHEN req_value.value IS NULL THEN "Any" ELSE req_value.value END AS required,
  CASE WHEN avail_value IS NULL THEN "None" ELSE avail_value.value END AS available;
Loading table...

Ewk! Salty butter! If we can get some baking powder and non-salty butter, we should be good to go.

MERGE (pancakeMaster:Chef {name: "Master of Pancakes"})
MERGE (powder:Ingredient {name: "Baking Powder"})
MERGE (butter:Ingredient {name: "Butter"})
WITH *
MATCH (pancakeMaster)-[:has_value]->(butter_val:Value)-[:is_part_of]->(butter)
SET butter_val.value = "Plain"
CREATE (pancakeMaster)-[:has_value]->(powder_val:Value)-[:is_part_of]->(powder)
RETURN *;

Et Voila, now the chef can work his magic:

MATCH (pancake:Recipe {title: "Ultimate Pancake Recipe"})-[:requires_value]->(req_value:Value)-[:is_part_of]->(ingredient:Ingredient)
OPTIONAL MATCH (ingredient)<-[:is_part_of]-(avail_value)<-[:has_value]-(pancakeMaster:Chef {name: "Master of Pancakes"})
WITH ingredient, req_value, avail_value
WHERE avail_value IS NULL OR req_value.value <> avail_value.value
RETURN
  ingredient.name AS ingredient,
  CASE WHEN req_value.value IS NULL THEN "Any" ELSE req_value.value END AS required,
  CASE WHEN avail_value IS NULL THEN "None" ELSE avail_value.value END AS available;
Loading table...

As you can see, it’s great fun cooking with Neo4j!

Run
Table
Graph
Table!
Graph!
Error!
Loading