def compatible_recipes(self, limit=10):
cur = self.manager.get_cursor()
cur.execute(
"""
SELECT
recipes.*,
owner.*,
SUM(LEAST(1.0, qtyowned / ownd_comp.amount)) / (
SELECT COUNT(iname)
FROM requires_ingredient
WHERE requires_ingredient.rid = recipes.rid
) AS percent_owned
FROM ingredient_ownership
JOIN requires_ingredient
AS ownd_comp
ON ownd_comp.iname = ingredient_ownership.iname
JOIN recipes
ON ownd_comp.rid = recipes.rid
JOIN users
AS owner
ON owner.uid = recipes.owner_id
WHERE ingredient_ownership.uid = %s
GROUP BY recipes.rid, owner.uid
ORDER BY percent_owned DESC
LIMIT %s;
""", (self.uid, limit))
results = ((Recipe.new_from_combined_record(self.manager,
record), record[8])
for record in cur.fetchall())
cur.close()
return results
def recent_recipes(self, user=None, limit=5):
cur = self.get_cursor()
cur.execute(
"""
SELECT
recipes.*,
owner.*,
MAX(datemade) as last_date
FROM recipes
JOIN dates_made ON recipes.rid = dates_made.rid
JOIN users
AS owner
ON owner.uid = recipes.owner_id
%%%
GROUP BY recipes.rid, owner.uid
ORDER BY last_date DESC
LIMIT %s;
""".replace('%%%',
('WHERE dates_made.uid = %s' if user != None else '')),
((limit, ) if user == None else (user.uid, limit)))
results = (Recipe.new_from_combined_record(self, record)
for record in cur.fetchall())
cur.close()
return results
def searchRecipes(self, name):
cur = self.get_cursor()
partialName = ['%' + x.strip() + '%' for x in name.split(' ')]
sqlQuery = """
SELECT recipes.*, owner.*
FROM recipes
JOIN users
AS owner
ON owner.uid = recipes.owner_id
WHERE """ + " OR ".join(["rName LIKE %s"] * len(partialName)) + ";"
cur.execute(sqlQuery, partialName)
records = cur.fetchall()
cur.close()
return (Recipe.new_from_combined_record(self, record)
for record in records)
def search_by_ingredient(self, ingr, limit=10):
cur = self.get_cursor()
cur.execute(
"""
SELECT recipes.*, owner.*
FROM requires_ingredient
JOIN recipes ON requires_ingredient.rid = recipes.rid
JOIN users
AS owner
ON owner.uid = recipes.owner_id
WHERE iname = %s
LIMIT %s;
""", (ingr.iname, limit))
results = (Recipe.new_from_combined_record(self, record)
for record in cur.fetchall())
cur.close()
return results
def recommended_recipes(self, limit=10):
cur = self.manager.get_cursor()
cur.execute(
"""
WITH user_commonality AS (
SELECT
others_made.uid as uid,
COUNT(others_made.rid)::float / (
SELECT COUNT(rid)
FROM dates_made
WHERE uid = others_made.uid
OR uid = %s
) AS percent_shared
FROM dates_made AS personally_made
JOIN dates_made
AS others_made
ON others_made.rid = personally_made.rid
WHERE personally_made.uid = %s
AND others_made.uid != %s
GROUP BY others_made.uid
),
users_by_recipe AS (
SELECT
uid,
rid,
LOG(COUNT(id)) + 1 as degree
FROM dates_made
GROUP BY uid, rid
),
recipe_ownership AS (
SELECT
recipes.rid,
SUM(LEAST(1.0, qtyowned / ownd_comp.amount)) / (
SELECT COUNT(iname)
FROM requires_ingredient
WHERE requires_ingredient.rid = recipes.rid
) AS percent_owned
FROM ingredient_ownership
JOIN requires_ingredient
AS ownd_comp
ON ownd_comp.iname = ingredient_ownership.iname
JOIN recipes
ON ownd_comp.rid = recipes.rid
WHERE ingredient_ownership.uid = %s
GROUP BY recipes.rid
),
scores AS (
SELECT
users_by_recipe.rid,
SUM(user_commonality.percent_shared * users_by_recipe.degree) as score,
SUM(percent_owned) AS percent_owned
FROM users_by_recipe
JOIN user_commonality ON users_by_recipe.uid = user_commonality.uid
JOIN recipe_ownership ON users_by_recipe.rid = recipe_ownership.rid
GROUP BY users_by_recipe.rid
)
SELECT
recipes.*,
users.*,
percent_owned,
(percent_owned/2 + 0.5) * score AS recommendation_degree
FROM scores
JOIN recipes ON recipes.rid = scores.rid
JOIN users ON recipes.owner_id = users.uid
ORDER BY recommendation_degree DESC;
""", [self.uid] * 4)
results = ((Recipe.new_from_combined_record(self.manager, record),
record[8], record[9]) for record in cur.fetchall())
cur.close()
return results
