def get_recipes(self, allergies, preferences, tags, order, start, limit):
"""Fetch all the initial information for the recipes excluding recipes
that have one of the user allergies.
"""
query = f'''SELECT recipes.id, recipes.name, recipes.url,
images.url AS image
FROM recipes
INNER JOIN images
ON recipes.image_id = images.id
WHERE recipes.id NOT IN (SELECT recipe_id
FROM recipes_allergies
WHERE allergy_id IN %s)
AND recipes.id NOT IN (SELECT recipe_id
FROM recipes_ingredients
WHERE ingredient_id IN %s)
AND recipes.id NOT IN (SELECT recipe_id
FROM recipes_tags
WHERE tag_id IN %s)
ORDER BY recipes.name {order}
LIMIT %s, %s'''
# Prefent passing a empy list, instead pass a non-existing id
if not allergies:
allergies = [0]
if not preferences:
preferences = [0]
if not tags:
tags = [0]
recipes = database.fetch_all(
query, (allergies, preferences, tags, start, limit))
return [Recipe(**recipe) for recipe in recipes]
def get_allergens(self, ingredient_id):
"""Get all allergens for an ingredient from the database and return a
list of instances of the allergy class.
"""
query = '''SELECT allergies.id, allergies.name
FROM allergies, allergies_ingredients
WHERE allergies_ingredients.allergy_id = allergies.id
AND allergies_ingredients.ingredient_id = %s'''
allergens = database.fetch_all(query, ingredient_id)
def get_tags(self, recipe_id):
"""Fetch all the extra info for a recipe (eg. main dish, desert, vegan)"""
query = '''SELECT tags.id, tags.name
FROM tags
INNER JOIN recipes_tags
ON recipes_tags.tag_id = tags.id
WHERE recipe_id = %s'''
tags = database.fetch_all(query, recipe_id)
# Convert the list of dicts to a list of tag objects
return [Tag(**tag) for tag in tags]
def get_allergies(self):
"""Get all allergies from the database and return a list of instances
of the allergy class.
"""
query = '''SELECT id, name
FROM allergies
ORDER BY name'''
allergies = database.fetch_all(query)
# Convert the list of dicts to a list of allergy objects
return [Allergy(**allergy) for allergy in allergies]
def get_ingredients(self):
"""Get all ingredients from the database and return a list of instances
of the ingredient class.
"""
query = '''SELECT id, name
FROM ingredients
ORDER BY name'''
ingredients = database.fetch_all(query)
# Convert the list of dicts to a list of ingredient objects
return [Ingredient(**ingredient) for ingredient in ingredients]
def get_preferences(self, id):
"""Get all preferences for a roommate from the database and return a
listof instances of the ingredient class.
"""
query = '''SELECT ingredients.id, ingredients.name
FROM ingredients, roommates_preferences AS rp
WHERE rp.roommate_id = %s
AND rp.ingredient_id = ingredients.id'''
preferences = database.fetch_all(query, id)
# Convert the list of dicts to a list of ingredient objects
return [Ingredient(**ingredient) for ingredient in preferences]
def get_allergies(self, id):
"""Get all allergies for a roommate from the database and return a list
of instances of the allergy class.
"""
query = '''SELECT allergies.id, allergies.name
FROM allergies, roommates_allergies AS ra
WHERE ra.roommate_id = %s
AND ra.allergy_id = allergies.id'''
allergies = database.fetch_all(query, id)
# Convert the list of dicts to a list of allergy objects
return [Allergy(**allergy) for allergy in allergies]
def get_preferences(self, user_id):
"""Get a list of preferences of a user from the database and return a
list of instances of the ingredient class.
"""
query = '''SELECT ingredients.id, ingredients.name
FROM ingredients, users_preferences
WHERE users_preferences.user_id = %s
AND users_preferences.ingredient_id = ingredients.id'''
ingredients = database.fetch_all(query, user_id)
# Convert the list of dicts to a list of ingredients objects
return [Ingredient(**ingredient) for ingredient in ingredients]
def get_allergies(self, user_id):
"""Get a list of allergies of an user from the database and return a
list of instances of the allergy class.
"""
query = '''SELECT allergies.id, allergies.name
FROM allergies, users_allergies
WHERE users_allergies.user_id = %s
AND users_allergies.allergy_id = allergies.id'''
allergies = database.fetch_all(query, user_id)
# Convert the list of dicts to a list of allergy objects
if allergies:
return [Allergy(**allergy) for allergy in allergies]
def get_roommates(self, user_id):
"""Get all roommates for a user from the database and return a list of
instances of the roommate class.
"""
query = '''SELECT id, handle,
user_id,
first_name, middle_name, last_name
FROM roommates
WHERE user_id = %s
ORDER BY handle'''
roommates = database.fetch_all(query, user_id)
# Convert the list of dicts to a list of roommate objects
return [Roommate(**roommate) for roommate in roommates]
def get_allergies(self, recipe_id):
"""Get all allergies from the database and return a list of instances
of the allergy class.
"""
query = '''SELECT DISTINCT allergies.id, allergies.name
FROM allergies
INNER JOIN recipes_allergies
ON recipes_allergies.allergy_id = allergies.id
WHERE recipe_id = %s
ORDER BY allergies.name'''
allergies = database.fetch_all(query, recipe_id)
# Convert the list of dicts to a list of allergy objects
return [Allergy(**allergy) for allergy in allergies]
def get_ingredients(self, recipe_id):
"""Fetch all ingredients from the database and return a list of
instances of the ingredient class.
"""
query = '''SELECT DISTINCT ingredients.id, ingredients.name
FROM ingredients
INNER JOIN recipes_ingredients
ON recipes_ingredients.ingredient_id = ingredients.id
WHERE recipe_id = %s
ORDER BY ingredients.name'''
ingredients = database.fetch_all(query, recipe_id)
# Convert the list of dicts to a list of ingredient objects
return [Ingredient(**ingredient) for ingredient in ingredients]
def search_recipes(self, allergies, preferences, tags, search, order,
start, limit):
"""Search all recipes. Filters on allergies, preferences and tags and
return list of instances of the recipe class.
"""
query = f'''SELECT recipes.id, recipes.name, recipes.url,
images.url AS image
FROM recipes
INNER JOIN images
ON recipes.image_id = images.id
WHERE recipes.id NOT IN (SELECT recipe_id
FROM recipes_allergies
WHERE allergy_id IN %s)
AND recipes.id NOT IN (SELECT recipe_id
FROM recipes_ingredients
WHERE ingredient_id IN %s)
AND recipes.id NOT IN (SELECT recipe_id
FROM recipes_tags
WHERE tag_id IN %s)
AND recipes.name LIKE %s
ORDER BY recipes.name {order}
LIMIT %s, %s'''
# Prefent passing a empy list, instead pass a non-existing id
if not allergies:
allergies = [0]
if not preferences:
preferences = [0]
if not tags:
tags = [0]
recipes = database.fetch_all(
query, (allergies, preferences, tags, f'%{search}%', start, limit))
return [Recipe(**recipe) for recipe in recipes]