I've already read this and this, but they don't solve my problem because they make a final "count" with a comparison to a number that is hard-coded. I want to make a comparison of a number that is the sum of all ingredients of the recipe itself.
Let's imagine that I have some ingredients in my fridge, with their id's (= array of id's). I want to see what I can cook with that. I have my models like this:
class Ingredient(models.Model):
label = models.CharField(max_length=200, null=True, blank=True,
default=None)
description = models.TextField(null=True, blank=True, default=None)
class Unit(models.Model):
label = models.CharField(max_length=200, null=True, blank=True,
default=None)
abbr = models.CharField(max_length=20, null=True, blank=True,
default=None)
class IngredientUnit(models.Model):
ingredient = models.ForeignKey(Ingredient, null=False, blank=True)
unit = models.ForeignKey(Unit, null=False, blank=True)
measurable = models.BooleanField(default=True, null=False, blank=True)
is_int = models.BooleanField(default=True, null=False, blank=True)
value = models.FloatField(null=True, blank=True, default=0.0)
class Recipe(models.Model):
label = models.CharField(max_length=200, null=True, blank=True,
default=None)
description = models.TextField(null=True, blank=True, default=None)
ingredients = models.ManyToManyField(IngredientUnit)
I'd like to do this: 'select all recipe that have all' the ingredients of an array of ingredient's pk. For example: "Classic Vanilla Cake" has those ingredients: eggs, all-purpose flour, baking powder, baking soda, butter, sugar, vanilla, buttermilk. If one is missing, "Classic Vanilla Cake" shouldn't be in the resulting query. On the contrary, if there are more ingredients than those required, "Classic Vanilla Cake" should always be in the resulting query.
So far, I've done this, but it's not working.
ingredient_ids = self.request.POST.getlist('ingredient[]', [])
if len(ingredient_ids):
recipes = Recipe.objects\
.filter(ingredients__in=ingredient_ids)\
.annotate(nb_ingredients=Count('ingredients'))\
.filter(nb_ingredients=len(ingredient_ids))
print([a for a in recipes])
The problem is that nb_ingredients=len(ingredient_ids) should be nb_ingredients=the number of the ingredients of the current recipe
How can I do this?
I found it! Can't avoid double query but it works like a charm. Here's the solution:
- first, filter on Ingredients that are part of a recipe, for each recipe (=
group by), count the total of ingredients found
- then for all the existing recipes, if the total of ingredient == the total of ingredients founds before, then it's ok, keep it.
It feels like using a sledgehammer to crack a nut (even though the first query filters and eliminates a lot of recipes), but it works, if you have a better solution I'm your man!
recipes = Recipe.objects \
.annotate(found=Count('*'))\
.filter(ingredients__in=ingredient_ids)
for recipe in recipes:
a = Recipe.objects.annotate(total=Count('ingredients')).filter(
pk=recipe.pk, total=recipe.found)
print("Recipe found:", str(a))
And for example, if the ids of the ingredients are [1, 2, 3, 4, 5] you'll get those two queries:
SELECT "app_recipe"."id", "app_recipe"."label", "app_recipe"."description",
COUNT(*) AS "found" FROM "app_recipe"
INNER JOIN "app_recipe_ingredients"
ON ("app_recipe"."id" = "app_recipe_ingredients"."recipe_id")
WHERE "app_recipe_ingredients"."ingredientunit_id" IN (1, 2, 3, 4, 5)
GROUP BY "app_recipe"."id", "app_recipe"."label", "app_recipe"."description";
And the second loop will make queries based on the recipes found like this:
SELECT "app_recipe"."id", "app_recipe"."label", "app_recipe"."description",
COUNT("app_recipe_ingredients"."ingredientunit_id") AS "total"
FROM "app_recipe"
LEFT OUTER JOIN "app_recipe_ingredients"
ON ("app_recipe"."id" = "app_recipe_ingredients"."recipe_id")
WHERE "app_recipe"."id" = 1
GROUP BY "app_recipe"."id", "app_recipe"."label", "app_recipe"."description"
HAVING COUNT("app_recipe_ingredients"."ingredientunit_id") = 5;
I think you can try to exclude the recipes which have missing ingredients.
Recipe.objects.exclude(~Q(ingredients__in=ingredient_ids)).distinct()
Tested it, its not working.
In theory, what you want:
The problem is that nb_ingredients=len(ingredient_ids) should be
nb_ingredients=the number of the ingredients of the current recipe
should be achieved with one annotation before the filter and one annotation after the filter (because the order of annotation and filter matters).
I.e. something like this:
recipes = (Recipe.objects
.annotate(num_ingredients=Count('ingredients'))
.filter(ingredients__in=ingredient_ids)
.annotate(found_ingredients=Count('ingredients'))
.filter(num_ingredients=F('found_ingredients'))
)
however when I tested this, it wasn't working because the ORM didn't reuse the correct JOIN from the filter. This could be some kind of a bug, I just open a question in the mailing list.
In case you are using Django 1.11, another approach would be use the new Subquery-expressions, can you try this.
recipes = (Recipe.objects
.filter(ingredients__in=ingredient_ids)
.annotate(found_ingredients=Count('ingredients', distinct=True))
.annotate(num_ingredients=Count(Subquery(
Ingredient.objects.filter(recipe=OuterRef('pk')).only('pk')),
distinct=True)
)
.filter(num_ingredients=F('found_ingredients'))
)
