def get_recipe_count(self, allergies, preferences, tags):
"""Fetch the total number of recipes excluding the recipes that contain
a user allergy.
"""
query = f'''SELECT COUNT(*) AS recipe_count
FROM recipes
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)'''
# 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]
result = database.fetch(query, (allergies, preferences, tags))
return result['recipe_count']
def verify_username(self, username):
"""Check if the username is valid and if it is not in use."""
query = '''SELECT COUNT(username) AS username_count
FROM users
WHERE username = %s'''
# Retrun number of matching usernames
return database.fetch(query, username)['username_count']
def verify_email(self, email):
"""Check if the email adress is valid and if it is not in use."""
# @TODO vefify email adress
query = '''SELECT COUNT(email) AS email_count
FROM users
WHERE email = %s'''
# Retrun number of matching email adresses
return database.fetch(query, email)['email_count']
def get_handle(self, user_id):
"""Get the highest roommate id for an user. This is used to genereate a
handle for a roommate.
"""
query = '''SELECT MAX(handle) + 1 AS handle
FROM roommates
WHERE user_id = %s'''
# Return the
return database.fetch(query, user_id)['handle']
def get_allergy(self, id):
"""Get an allergy from the database and return an instance of the
allergy class.
"""
query = '''SELECT id, name
FROM allergies
WHERE id = %s'''
# Convert dict to an allergy object
return Allergy(**database.fetch(query, id))
def get_ingredient(self, id):
"""Get an ingredient from the database and return an instance of the
ingredient class.
"""
query = '''SELECT id, name
FROM ingredients
WHERE id = %s'''
# Convert dict to an ingredient object
return Ingredient(**database.fetch(query, id))
def get_roommate(self, user_id, handle):
"""Get a roommate from the database and return an instance of the
roommate class.
"""
query = '''SELECT id, handle,
user_id,
first_name, middle_name, last_name
FROM roommates
WHERE handle = %s
AND user_id = %s'''
# Convert dict to a roommate object
return Roommate(**database.fetch(query, (handle, user_id)))
def get_user_by_username(self, username):
"""Get an user from the database and return an instance of the user
class.
"""
query = '''SELECT id,
username,
email,
first_name, middle_name, last_name,
hash
FROM users
WHERE username = %s'''
# Convert dict to an user object
return User(**database.fetch(query, username))