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