简化的型号如下。基本上可以这样认为：您有一堆食谱，并且有一些用户可以上载他们在厨房中拥有的所有配料。然后，您可以告诉他们要使用哪些食谱（以及食谱是否包含所有食谱）。

模型

class Recipe(models.Model):
    name = models.CharField(max_length=255)
    ingredients = models.ManyToManyField(
        'Ingredient',blank=True,through='RecipeIngredient')

class Ingredient(models.Model):
    name = models.CharField(max_length=255)
    type = models.CharField(max_length=255,blank=True)

class RecipeIngredient(models.Model):
    recipe = models.ForeignKey(Recipe)
    ingredient = models.ForeignKey(Ingredient)
    amount = models.FloatField(_('amount'),null=True,blank=True)
    unit = models.ForeignKey(Unit,null=True)

class UserIngredient(models.Model):

    LIST_TYPE_CHOICES = [
        ('PANTRY','My Pantry'),('SHOPPING_LIST','My Shopping List'),]

    user = models.ForeignKey(
        settings.AUTH_USER_MODEL,on_delete=models.CASCADE,related_name=RELATED_NAME)
    ingredient = models.ForeignKey(
        Ingredient,related_name=RELATED_NAME)
    list_type = models.CharField(max_length=255,choices=LIST_TYPE_CHOICES)

SQL /查询集

SELECT
    r.id,r.name,COUNT(1) AS num_ingredients,COUNT(ui.ingredient_id) AS num_matched 
FROM
    core_recipe AS r 
INNER JOIN
    core_recipeingredient AS ri 
        ON r.id = ri.recipe_id 
LEFT JOIN
    users_useringredient AS ui 
        ON (
            ri.ingredient_id = ui.ingredient_id
        ) 
        AND ui.user_id = '<user_id>' 
        AND ui.list_type = 'PANTRY'
GROUP BY
    r.id,r.name 
HAVING
    COUNT(1) = COUNT(ui.ingredient_id)
    # Or,if we want Recipes the user has ANY ingredients for:
    # COUNT(ui.ingredient_id) > 0

我现在拥有的查询集可以尝试重现此内容：

user_filter = Q(ingredients__useringredients__user=self.request.user,ingredients__useringredients__list_type='PANTRY')

# Specificy values we need. Also mandatory.
queryset = Recipe.objects.values('id','name')

# Add COUNT aggregations via annotate for number of ingredients
# in the recipe and number of ingredients the user has (from the
# recipe).
queryset = queryset.annotate(
    num_ingredients=Count('ingredients'),num_user_ingredients=Count('ingredients__useringredients',filter=user_filter))

# Add HAVING clause of the query. We either return recipes
# that user has all ingredients for,or any ingredients for.
# This is based on the query params being sent (e.g. ?any).
if 'any' in self.request.GET:
    queryset = queryset.filter(num_user_ingredients__gt=0)
else:
    queryset = queryset.filter(
        num_ingredients=F('num_user_ingredients'))

如果我们打印查询集的query属性，则会得到：

SELECT
  "core_recipe"."id","core_recipe"."name",COUNT("core_recipeingredient"."ingredient_id") AS "num_ingredients",COUNT("users_useringredient"."id")
    FILTER (WHERE ("users_useringredient"."list_type" = PANTRY
      AND "users_useringredient"."user_id" = <user_id>)) AS "num_user_ingredients"
FROM
  "core_recipe"
  LEFT OUTER JOIN "core_recipeingredient"
    ON ("core_recipe"."id" = "core_recipeingredient"."recipe_id")
  LEFT OUTER JOIN "core_ingredient"
    ON ("core_recipeingredient"."ingredient_id" = "core_ingredient"."id")
  LEFT OUTER JOIN "users_useringredient"
    ON ("core_ingredient"."id" = "users_useringredient"."ingredient_id")
GROUP BY
  "core_recipe"."id"
HAVING
  COUNT("core_recipeingredient"."ingredient_id") = (COUNT("users_useringredient"."id")
    FILTER (WHERE ("users_useringredient"."list_type" = PANTRY
      AND "users_useringredient"."user_id" = <user_id>)))

哪个真的很近。但是，请注意，users_useringredient的JOIN并不是由user_id和list_type过滤的，只有COUNT和HAVING是通过FILTER子句进行的。当一个成分在多个用户列表中时，这会导致JOIN爆炸。

我沿着FilteredRelation的路走了，但是它现在只能深入到一个级别，所以我不知道在这种情况下如何使用它。

我也沿着Raw SQL的路走了，但是很快意识到（我认为？）使该组合成为可能是不可能的，例如我如何根据查询参数更改如上所示的HAVING子句？

任何想出最佳方法的人都会很有帮助。谢谢！