Saya memiliki monster pernyataan if yang saya gunakan untuk memfilter hasil dari database, tetapi meminta untuk di-refactored, apakah ada cara untuk membuat kueri filter bersyarat pada variabel? Saya perlu mengembalikan resep milik setiap kategori/masakan jika memiliki ID "1" yang semuanya ada di DB saya.

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()
1
mobile_developer42 21 September 2019, 19:01

1 menjawab

Jawaban Terbaik

Mencoba:

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()

Saya menyarankan mengganti nama variabel cuisine_form menjadi cuisine_id dll. Karena penamaan *_form untuk variabel yang memiliki nilai id cukup menyesatkan.

Juga literal "1" dan "2" harus diganti dengan konstanta dengan nama deskriptif yang tepat sehingga langsung jelas apa yang ditunjukkan oleh nilai-nilai ini.

0
stasiekz 21 September 2019, 16:45