Querying django models to include and exclude items

Question

I currently have 2 models as such

class Recipe(models.Model):
    account = models.ForeignKey(CustomUser, on_delete=models.CASCADE, null=True, blank=True)
    name = models.TextField(null=True, blank=True)
    slug = models.SlugField(null=False, blank=True, unique=True)
    image_path = models.ImageField(upload_to=MEDIA_URL, null=True, blank=True)
    description = models.TextField(null=True, blank=True)
    date_added = models.DateField(auto_now_add=True)

class RecipeIngredients(models.Model):
    recipe = models.ForeignKey(Recipe, on_delete=models.CASCADE, null=True)
    ingredient = models.TextField(null=True, blank=True)
    quantity = models.CharField(max_length=10, null=True, blank=True)
    type = models.CharField(max_length=50, null=True, blank=True)

I am trying to do a query where if I have a list of say 2 or more items, say

ingredients = ["egg", "bacon", "rice"]

That it returns to me only the recipes that have exactly egg, bacon, and rice, or less.

I was able to do this in a hacky way, but it is really slow and not using the ORM correctly I feel.

ingredients = ["egg", "bacon", "rice"]
results = []
recipes = []
for i in ingredients:
    r = RecipeIngredients.objects.filter(ingredient__icontains=i)
    results.append(r)
for result in results:
    for r in result:
        recipes.append(r.recipe)
for r in recipes:
    recipeingredients = r.recipeingredients_set.all()
    for ri in recipeingredients:
        ingredient = ri.ingredient
        if ingredient not in ingredients:
            try:
                recipes.remove(r)
            except:
                print(r)
    print("end of recipe")

Any help on how to make this a more correct query would be appreciated.

score 1 · Accepted Answer · answered May 26 '22 at 01:38

1

You can use raw sql, something like this:

recipe_list = Recipe.objects.raw('select a.*
   from app_Recipe a 
   inner join app_RecipeIngredients b
      on a.id = b.recipe_id and b.ingredient in ("egg", "bacon", "rice")
   group by a.*
   having count(*) >= 2')

maybe replace app_ with your project name, replace a.* with list of column names.

answered May 26 '22 at 01:38

AIMIN PAN

1,563
1
9
13

For a explainer of converting Django ORM to raw see https://stackoverflow.com/questions/23887559/how-to-pass-a-list-of-values-for-an-in-sql-clause-in-django – nadermx May 30 '22 at 19:20

Querying django models to include and exclude items

1 Answers1

Linked