Food network import instructions

foodimport.cql

// create nodes
// create BaseNodes
import-cypher -d ; -i ./IMPORT/INPUT/1-basenodes.csv -o ./IMPORT/OUTPUT/nodeoutput.csv create (n:#{type} {id:{id},name:{name}}) return n

// create indexes
create index on :INGREDIENT_CATEGORY(name);
create index on :INGREDIENT(name);
create index on :INGREDIENT(id);
create index on :COMPOUND(name);
create index on :COMPOUND(id);
create index on :AREA(name);
create index on :CUISINE(name);
create index on :RECIPE(cuisine);
create index on :RECIPE(id);
create index on :RECIPE_SOURCE(name);

// create recipes
import-cypher -d ; -i ./IMPORT/INPUT/2-recipes.csv -o ./IMPORT/OUTPUT/nodeoutput.csv create (n:RECIPE {id:{id},source:{source},cuisine:{cuisine}}) return n

//create recipe sources
match (n:RECIPE) 
with distinct n.source as recipe_source
create (recipesource:RECIPE_SOURCE {name:recipe_source});

//linke recipes to recipe sources
match (n:RECIPE), (m:RECIPE_SOURCE)
where n.source=m.name
with n as recipe, m as source
create source-[:IS_SOURCE_OF]->recipe;

// link recipe to cuisine
import-cypher -d ; -i ./IMPORT/INPUT/2-recipes.csv -o ./IMPORT/OUTPUT/nodeoutput.csv match (cuisine:CUISINE {name:{cuisine}}), (n:RECIPE {id:{id}}) create n-[:OF_CUISINE]->cuisine return n

// link ingredients to ingredient categories
import-cypher -d ; -i ./IMPORT/INPUT/3-ingred_belongsto_ingredcat.csv -o ./IMPORT/OUTPUT/nodeoutput.csv match (ingred:INGREDIENT {name:{ingredient_name}}), (ingredcat:INGREDIENT_CATEGORY {name:{ingredientcategory_name}}) create ingred-[r:BELONGS_TO]->ingredcat return r 

//link COMPOUNDs to ingredients
import-cypher -d ; -i ./IMPORT/INPUT/4-ingred_has_compound.csv -o ./IMPORT/OUTPUT/nodeoutput.csv match (compound:COMPOUND {id:{compound_id}}), (ingred:INGREDIENT {id:{ingredient_id}}) create compound-[r:COMP_PART_OF]->ingred return r

//link cuisines to areas
import-cypher -d ; -i ./IMPORT/INPUT/5-cuisine_locatedin_area.csv -o ./IMPORT/OUTPUT/nodeoutput.csv match (cuisine:CUISINE {name:{cuisine_name}}), (area:AREA {name:{area_name}}) create cuisine-[r:LOCATED_IN]->area return r

//link ingredients to recipes
import-cypher -d ; -i ./IMPORT/INPUT/6-ingred_partof_recipe.csv -o ./IMPORT/OUTPUT/nodeoutput.csv match (ingred:INGREDIENT {name:{ingredient_name}}), (recipe:RECIPE {id:{recipe_id}}) create ingred-[r:INGR_PART_OF]->recipe return r

foodqueries.cql

//what labels are there and how many nodes?
MATCH (n) RETURN DISTINCT labels(n) AS Label, count(n) AS Nr
ORDER by Nr DESC;

// What is related, and how
MATCH (a)-[r]->(b)
RETURN DISTINCT head(labels(a)) AS This, type(r) AS To, head(labels(b)) AS That
LIMIT 100;

//How many recipes in an area
MATCH (area:AREA)<-[:LOCATED_IN]-(cuisine:CUISINE)<-[:OF_CUISINE]-(recipe:RECIPE)
RETURN area.name AS Area, count(DISTINCT recipe) AS NumberOfRecipes
ORDER BY NumberOfRecipes DESC;

//How many recipes with ingredients from different ingredient categories
MATCH (recipe:RECIPE)<-[:INGR_PART_OF]-(ingredient:INGREDIENT)-[:BELONGS_TO]->(ingredcat:INGREDIENT_CATEGORY)
RETURN ingredcat.name AS IngredientCategory, count(DISTINCT recipe) AS NumberOfRecipes
ORDER BY NumberOfRecipes DESC;

//How many recipes in an area that use a specific ingredient category
MATCH (area:AREA)<-[:LOCATED_IN]-(cuisine:CUISINE)<-[:OF_CUISINE]-(recipe:RECIPE)
WITH recipe, area
MATCH (recipe)<-[:INGR_PART_OF]-(ingredient:INGREDIENT)-[:BELONGS_TO]->(ingredcat:INGREDIENT_CATEGORY)
RETURN area.name AS Area, ingredcat.name AS IngredientCategory, count(DISTINCT recipe) AS NumberOfRecipes
ORDER BY NumberOfRecipes DESC;

//what are the top 10 ingredient categories used in a Western Europe
MATCH (area:AREA {name:"WesternEuropean"})<-[:LOCATED_IN]-(cuisine:CUISINE)<-[:OF_CUISINE]-(recipe:RECIPE)
WITH recipe, area
MATCH (recipe)<-[:INGR_PART_OF]-(ingredient:INGREDIENT)-[:BELONGS_TO]->(ingredcat:INGREDIENT_CATEGORY)
RETURN area.name AS Area, ingredcat.name AS IngredientCategory, count(DISTINCT recipe) AS NumberOfRecipes
ORDER BY Area ASC, NumberOfRecipes DESC;

//How many recipes do we have for Belgian and Dutch cuisine
MATCH (cuisine:CUISINE {name:"Belgium"})<-[:OF_CUISINE]-(recipe:RECIPE) return count(recipe);
MATCH (cuisine:CUISINE {name:"Netherlands"})<-[:OF_CUISINE]-(recipe:RECIPE) return count(recipe);

//what are the top 10 ingredient categories used in Belgium
MATCH (cuisine:CUISINE {name:"Belgium"})<-[:OF_CUISINE]-(recipe:RECIPE)
WITH recipe, cuisine
MATCH (recipe)<-[:INGR_PART_OF]-(ingredient:INGREDIENT)-[:BELONGS_TO]->(ingredcat:INGREDIENT_CATEGORY)
RETURN cuisine.name AS Cuisine, ingredcat.name AS IngredientCategory, count(DISTINCT recipe) AS NumberOfRecipes
ORDER BY Cuisine ASC, NumberOfRecipes DESC;

//what are the top ingredient categories used in Belgium AND the Netherlands
MATCH (cuisine:CUISINE)<-[:OF_CUISINE]-(recipe:RECIPE)
WHERE cuisine.name="Belgium" or cuisine.name="Netherlands"
WITH recipe, cuisine 
MATCH (recipe)<-[:INGR_PART_OF]-(ingredient:INGREDIENT)-[:BELONGS_TO]->(ingredcat:INGREDIENT_CATEGORY)
RETURN cuisine.name AS Cuisine, ingredcat.name AS IngredientCategory, count(DISTINCT recipe) AS NumberOfRecipes
ORDER BY Cuisine ASC, NumberOfRecipes DESC;

MATCH (cuisine:CUISINE)<-[:OF_CUISINE]-(recipe:RECIPE)
WHERE cuisine.name="Belgium" or cuisine.name="Netherlands"
WITH recipe, cuisine
MATCH (recipe)<-[r:INGR_PART_OF]-(ingredient:INGREDIENT)-[s:BELONGS_TO]->(ingredcat:INGREDIENT_CATEGORY)
RETURN cuisine, ingredcat,r,s;

//what are the top 10 ingredients used in Belgium
MATCH (cuisine:CUISINE {name:"Belgium"})<-[:OF_CUISINE]-(recipe:RECIPE)<-[:INGR_PART_OF]-(ingredient:INGREDIENT)
RETURN cuisine.name AS Cuisine, ingredient.name AS Ingredient, count(DISTINCT recipe) AS NumberOfRecipes
ORDER BY Cuisine ASC, NumberOfRecipes DESC;

//what are the top 10 ingredients used in Belgium and Netherlands
MATCH (belgium:CUISINE {name:"Belgium"})<-[:OF_CUISINE]-(recipe:RECIPE)<-[:INGR_PART_OF]-(ingredient:INGREDIENT)-[:INGR_PART_OF]->(recipe2:RECIPE)-[:OF_CUISINE]->(netherlands:CUISINE {name:"Netherlands"})
RETURN ingredient.name AS Ingredient, count(DISTINCT recipe) AS NumberOfSharedRecipes
ORDER BY NumberOfSharedRecipes DESC,Ingredient ASC;

//what are the top 10 ingredients used in England+Scotland and Ireland
MATCH (uk:CUISINE {name:"English_Scottish"})<-[:OF_CUISINE]-(recipe:RECIPE)<-[:INGR_PART_OF]-(ingredient:INGREDIENT)-[:INGR_PART_OF]->(recipe2:RECIPE)-[:OF_CUISINE]->(ireland:CUISINE {name:"Irish"})
RETURN ingredient.name AS Ingredient, count(DISTINCT recipe) AS NumberOfSharedRecipes
ORDER BY NumberOfSharedRecipes DESC,Ingredient ASC;


//what are the top 10 compounds used in a Western Europe
MATCH (area:AREA {name:"WesternEuropean"})<-[:LOCATED_IN]-(cuisine:CUISINE)<-[:OF_CUISINE]-(recipe:RECIPE)
WITH recipe, area
MATCH (recipe)<-[:INGR_PART_OF]-(ingredient:INGREDIENT)<-[:COMP_PART_OF]->(comp:COMPOUND)
RETURN area.name AS Area, comp.name AS Compound, count(DISTINCT recipe) AS NumberOfRecipes
ORDER BY Area ASC, NumberOfRecipes DESC
limit 20;

MATCH (area:AREA)<-[:LOCATED_IN]-(cuisine:CUISINE)<-[:OF_CUISINE]-(recipe:RECIPE)
WITH recipe, area
MATCH (recipe)<-[:INGR_PART_OF]-(ingredient:INGREDIENT)<-[:COMP_PART_OF]->(comp:COMPOUND)
RETURN area.name AS Area, comp.name AS Compound, count(DISTINCT recipe) AS NumberOfRecipes
ORDER BY Area ASC, NumberOfRecipes DESC;

//find recipes with similar ingredients as "Salade Liègeoise"
MATCH (bacon:INGREDIENT {name:"bacon"})-[:INGR_PART_OF]->(recipe:RECIPE)<-[INGR_PART_OF]-(potato:INGREDIENT 
	{name:"potato"}), (bean:INGREDIENT {name:"bean"})-[:INGR_PART_OF]->(recipe) 
WITH recipe
MATCH (area:AREA)<-[:LOCATED_IN]-(cuisine:CUISINE)<-[:OF_CUISINE]-(recipe)<-[INGR_PART_OF]-(listofingredients:INGREDIENT)
RETURN area.name AS Area, cuisine.name AS Cuisine, recipe.id AS Recipe, collect(listofingredients.name) AS ListOfIngredients
ORDER BY Area ASC, Cuisine ASC;

//find recipes with similar ingredients as "Salade Liègeoise", using different types of bacon, potato and beans
MATCH (bacon:INGREDIENT)-[:INGR_PART_OF]->(recipe:RECIPE)<-[INGR_PART_OF]-(potato:INGREDIENT), (bean:INGREDIENT)-[:INGR_PART_OF]->(recipe) 
WHERE bacon.name =~ ".*bacon.*" AND potato.name =~ ".*potato.*" AND bean.name =~ ".*bean.*"
WITH recipe
MATCH (area:AREA)<-[:LOCATED_IN]-(cuisine:CUISINE)<-[:OF_CUISINE]-(recipe)<-[INGR_PART_OF]-(listofingredients:INGREDIENT)
RETURN area.name AS Area, cuisine.name AS Cuisine, recipe.id AS Recipe, collect(listofingredients.name) AS ListOfIngredients
ORDER BY Area ASC, Cuisine ASC;

//find recipes with specific ingredients from different areas for visualisation in browser
MATCH (bacon:INGREDIENT {name:"bacon"})-[:INGR_PART_OF]->(recipe:RECIPE)<-[INGR_PART_OF]-(potato:INGREDIENT {name:"potato"}), (bean:INGREDIENT {name:"bean"})-[:INGR_PART_OF]->(recipe) 
WITH recipe
MATCH (area:AREA)<-[:LOCATED_IN]-(cuisine:CUISINE)<-[:OF_CUISINE]-(recipe)<-[INGR_PART_OF]-(listofingredients:INGREDIENT)
RETURN area, cuisine, recipe, listofingredients;

//find recipes with specific ingredients from different areas for visualisation in browser - with different types of bacon, potato, beans
MATCH (bacon:INGREDIENT)-[:INGR_PART_OF]->(recipe:RECIPE)<-[INGR_PART_OF]-(potato:INGREDIENT), (bean:INGREDIENT)-[:INGR_PART_OF]->(recipe) 
WHERE bacon.name =~ ".*bacon.*" AND potato.name =~ ".*potato.*" AND bean.name =~ ".*bean.*"
WITH recipe
MATCH (area:AREA)<-[:LOCATED_IN]-(cuisine:CUISINE)<-[:OF_CUISINE]-(recipe)<-[INGR_PART_OF]-(listofingredients:INGREDIENT)
RETURN area, cuisine, recipe, listofingredients;