Django ORM __in but instead of exact, contains case insensative?

May 30, 2022

I am currently trying to use the Django ORM to query the Recipe model’s ingredients.

class Recipe(models.Model):
    account = models.ForeignKey(CustomUser, on_delete=models.CASCADE, null=True, blank=True)
    name = models.TextField(null=True, blank=True)

class RecipeIngredients(models.Model):
    recipe = models.ForeignKey(Recipe, on_delete=models.CASCADE, null=True)
    ingredient = models.TextField(null=True, blank=True)

What I have so far is


ingredients = ["eggs", "bacon", "potato"]
recipes = Recipe.objects.filter(
    recipeingredients__ingredient__in=ingredients
).alias(
    ningredient=Count('recipeingredients')
).filter(
    ningredient__gte=len(ingredients)
)

From my understanding of this answer this will return all the items that contain only "eggs", "bacon", and "potato", but not say Eggs or Scrambled EGGS. Is there anyway to adjust this to have it search for all items that contains the ingredients and case insensative?

>Solution :

You can create a disjunction of conditions, with:

from django.db.models import Q

ingredients = ['eggs', 'bacon', 'potato']

recipes = Recipe.objects.filter(
    Q(
        *[('recipeingredients__ingredient__icontains', ingredient) for ingredient in ingredients],
        _connector=Q.OR
    )
).alias(
    ningredient=Count('recipeingredients')
).filter(
    ningredient__gte=len(ingredients)
)

A potential problem might be that if the query is eggs, and two ingredients match, like 'white eggs' and 'brown eggs', these will count as two, and therefore another ingredient, like bacon might not be an ingredient, and will still be part of the QuerySet, so unfortunately, it is not easy to make a QuerySet that matches exactly all ingredients.

A potential solution could be:

ingredients = ['eggs', 'bacon', 'potato']

recipes = Recipe.objects.all()

for ingredient in ingredients:
    recipes = recipes.filter(recipeingredients__ingredient__icontains=ingredient)

But this will make n JOINs for n ingredients, and thus can easily become infeasible for a large amount of ingredients.