def get_bad_products(category: str, session: Session) -> List[Product]:
"""
Get bad products from given category in user database.
:param category: Category to filter on
:param session: Session
:return: List[Product]
"""
stmt = """
SELECT
p.brands,
'',
p.id,
p.nutriscore_grade,
p.packaging_tags,
p.product_name_fr,
'',
p.url
FROM Product_Category_Association as pca
INNER JOIN Products as p
ON pca.product_id = p.id
INNER JOIN Categories as c
ON pca.category_id = c.id
WHERE
pca.category_id = (SELECT id FROM Categories WHERE name = %s)
AND 'D' <= p.nutriscore_grade
"""
products = session.select(stmt, (category, ))
bad_products: List[Product] = choices(
[Product(*args) for args in products], k=ITEM_DISPLAYED)
return bad_products
def get_saved_products(session: Session) -> List:
"""
Get all previously saved products.
:param session: Session
:return: List[Product]
"""
stmt = """
SELECT
p.product_name_fr,
p2.product_name_fr,
rp.date
FROM Registered_Products as rp
INNER JOIN Products as p
ON rp.product_tested = p.id
INNER JOIN Products as p2
ON rp.product_substitued = p2.id
"""
return session.select(stmt)
def get_better_product(product: Product, session: Session) -> Product:
"""
Get a better product in replacement.
:param product: Product to replace
:param session: Session
:return: None
"""
stmt = """
SELECT
p.brands,
'',
p.id,
p.nutriscore_grade,
p.packaging_tags,
p.product_name_fr,
s.name,
p.url
FROM Products as p
INNER JOIN Product_Category_Association as pca
ON pca.product_id = p.id
INNER JOIN Categories as c
ON pca.category_id = c.id
INNER JOIN Product_Store_Association as psa
ON psa.product_id = p.id
INNER JOIN Stores as s
ON psa.store_id = s.id
WHERE
pca.category_id = (SELECT Category_id
FROM Product_Category_Association
WHERE Product_id = %s LIMIT 1)
AND 'B' >= p.nutriscore_grade
"""
products = session.select(stmt, (product.id, ))
better_product = choice([Product(*args) for args in products])
return better_product