python - SQLAlchemy 中的条件过滤

Question

我有一个 if 语句怪物，我用它来过滤数据库中的结果，但它需要重构，有什么方法可以使过滤器查询以变量为条件吗？如果 ID 为“1”，我需要返回属于每个类别/菜肴的食谱，该食谱都在我的数据库中。

if ingredients_form and any_ingredients == "1":
    if category_form == "1" and cuisine_form == "1":
        search_result2 = Recipe.query.filter(
            Recipe._ingredients.any(Ingredient.id.in_(ingredients_form)),
            ~Recipe._allergens.any(Allergen.id.in_(allergens_form))
            ).all()
    elif category_form == "1":
        search_result2 = Recipe.query.filter(
            Recipe._ingredients.any(Ingredient.id.in_(ingredients_form)),
            ~Recipe._allergens.any(Allergen.id.in_(allergens_form)),
            Recipe.category_id==category_form
            ).all()
    elif cuisine_form == "1":
        search_result2 = Recipe.query.filter(
            Recipe._ingredients.any(Ingredient.id.in_(ingredients_form)),
            ~Recipe._allergens.any(Allergen.id.in_(allergens_form)),
            Recipe.cuisine_id==cuisine_form
            ).all()
    else:
        search_result2 = Recipe.query.filter(
            Recipe._ingredients.any(Ingredient.id.in_(ingredients_form)),
            ~Recipe._allergens.any(Allergen.id.in_(allergens_form)), 
            Recipe.category_id==category_form,
            Recipe.cuisine_id==cuisine_form
            ).all()

elif ingredients_form and any_ingredients == "2":
    if category_form == "1" and cuisine_form == "1":
        search_result2 = Recipe.query.filter(
            Recipe._ingredients.any(Ingredient.id.in_(ingredients_form)),
            ~Recipe._allergens.any(Allergen.id.in_(allergens_form))
            ).all()
    elif category_form == "1":
        search_result2 = Recipe.query.filter(
            Recipe._ingredients.any(Ingredient.id.in_(ingredients_form)),
            ~Recipe._allergens.any(Allergen.id.in_(allergens_form)),
            Recipe.category_id==category_form
            ).all()
    elif cuisine_form == "1":
        search_result2 = Recipe.query.filter(
            Recipe._ingredients.any(Ingredient.id.in_(ingredients_form)),
            ~Recipe._allergens.any(Allergen.id.in_(allergens_form)),
            Recipe.cuisine_id==cuisine_form
            ).all()
    else:
        search_result2 = Recipe.query.filter(
            Recipe._ingredients.any(Ingredient.id.in_(ingredients_form)),
            ~Recipe._allergens.any(Allergen.id.in_(allergens_form)), 
            Recipe.category_id==category_form,
            Recipe.cuisine_id==cuisine_form
            ).all()

Answer 1

尝试:

import sqlalchemy

def custom_filter_statement(category_form, cuisine_form):
    if category_form == "1" and cuisine_form == "1":
        return sqlalchemy.sql.true()
    elif category_form == "1":
        return Recipe.category_id == category_form
    elif cuisine_form == "1":
        return Recipe.cuisine_id == cuisine_form
    return sqlalchemy.and_(Recipe.category_id==category_form, Recipe.cuisine_id==cuisine_form)

if ingredients_form and any_ingredients in ["1", "2"]:
    search_result2 = Recipe.query.filter(
        Recipe._ingredients.any(Ingredient.id.in_(ingredients_form)),
        ~Recipe._allergens.any(Allergen.id.in_(allergens_form)),
        custom_filter_statement(category_form, cuisine_form)
        ).all()

我建议将变量 cuisine_form 重命名为 kitchen_id 等。因为为保存 id 值的变量命名 *_form 非常具有误导性。

此外，文字 "1" 和 "2" 也应替换为具有正确描述性名称的常量，以便立即清楚这些值所表示的含义。