列表中（*）的*所有*项目的Django过滤器查询集__in [英] Django filter queryset __in for *every* item in list (2.0)

问题描述

我已经阅读了 this 和此，但是它们不能解决我的问题因为它们通过与硬编码的数字进行比较来进行最终的计数。我想对一个数字进行比较，该数字是食谱本身所有成分的总和。

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.

让我们想象一下我的冰箱里有一些成分，它们的ID是（= ID的数组）。我想看看我能做些什么。我有这样的模型：

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)    

我想这样做：'选择所有具有 all 的所有配方'。例如：经典香草蛋糕具有以下成分：鸡蛋，通用面粉，发酵粉，小苏打，黄油，糖，香草，酪乳。如果缺少一个，则结果查询中不应包含经典香草蛋糕。相反，如果配料中所含的成分更多，则 Classic Vanilla Cake应始终出现在结果查询中。

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

问题是 nb_ingredients = len（ingredient_ids）应该是 nb_ingredients =当前食谱的成分

我该怎么做？

推荐答案

我找到了！无法避免重复查询，但它就像一个符咒。解决方法如下：

I found it! Can't avoid double query but it works like a charm. Here's the solution:

• 首先，针对每个食谱（= 组）过滤属于食谱一部分的成分by ），计算找到的配料总数


• 然后，对于所有现有配方，如果配料总数==总数

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

例如，如果配料的ID为 [1， 2，3，4，5] t这两个查询：

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;

这篇关于列表中（*）的*所有*项目的Django过滤器查询集__in的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！