带注释条件表达式的Django查询使用INNER JOIN。如何让它使用OUTER JOIN？

I have a "Meal" model with a foreign key to "Food". Each meal has a rating: good, bad, or indifferent. I want to query a list of all foods and annotate the count of each type of meal rating, but some foods have no meals yet, so I want the query to use a LEFT OUTER JOIN and in that case the counts should be zero.

我有一个带有“Food”外键的“Meal”模型。每顿饭都有一个等级：好，坏，或无动于衷。我想查询所有食物的列表并注释每种类型的膳食评级的数量，但是一些食物还没有进餐，所以我希望查询使用LEFT OUTER JOIN，在这种情况下，计数应该为零。

I am using Conditional Expressions in Django 1.8, and it always switches the relationship to an INNER JOIN between "Food" and "Meal". For example:

我在Django 1.8中使用Conditional Expressions，它总是将关系切换为“Food”和“Meal”之间的INNER JOIN。例如：

Meal model:

膳食模型：

class Meal(models.Model):
    GOOD = 1
    BAD = 2
    INDIFFERENT = 3
    RATING_CHOICES = (
        (GOOD, 'Good'),
        (BAD, 'Bad'),
        (INDIFFERENT, 'Indifferent')
    )
    meal_time = models.DateTimeField()
    food = models.ForeignKey("Food")
    rating = models.IntegerField(blank=True, null=True, choices=RATING_CHOICES)

When I query Food.objects.annotate(total_meals=Count('meal')), Django generates a query like

当我查询Food.objects.annotate（total_meals = Count（'meal'））时，Django会生成一个类似的查询

SELECT ... FROM "Food" 
LEFT OUTER JOIN "Meal" ON ... 
GROUP BY "Food"

However, when I add these conditional annotations:

但是，当我添加这些条件注释时：

class FoodQuerySet(models.QuerySet):
    def with_meal_rating_frequency(self):
        return self.annotate(
            total_meals=Count('meal'),
            good_meals=Sum(
                 Case(When(meal__rating=Meal.GOOD, then=1),
                    output_field=models.IntegerField(), default=0)
            ),
            bad_meals=Sum(
                Case(When(meal__rating=Meal.BAD, then=1),
                    output_field=models.IntegerField(), default=0)
            ),
            indifferent_meals=Sum(
                Case(When(meal__rating=Meal.INDIFFERENT, then=1),
                    output_field=models.IntegerField(), default=0)
            )
        )

Django uses and INNER JOIN instead.

Django使用和INNER JOIN代替。

SELECT ... FROM "Food"
INNER JOIN "Meal" ON ...
GROUP BY "Food"

I know this question is very similar to this one but Its not clear to me how to apply the accepted solution to my case. How can I get Django to use a LEFT OUTER JOIN? Your help is appreciated, thanks!

我知道这个问题与这个问题非常相似，但我不清楚如何将接受的解决方案应用于我的案例。如何让Django使用LEFT OUTER JOIN？感谢您的帮助！

1 个解决方案

#1

I have found a solution that seems to be working so far, using Count() instead of Sum() and having the conditions check for NULL meals, which won't be included in the count:

我找到了一个似乎工作到目前为止的解决方案，使用Count（）而不是Sum（）并且条件检查NULL餐，这将不包括在计数中：

class FoodQuerySet(models.QuerySet):
    def with_meal_rating_frequency(self):
        return self.annotate(
            total_meals=Count('meal'),
            good_meals=Count(
                Case(When(Q(meal__isnull=True) | Q(meal__rating=Meal.GOOD), then='meal__rating'),
                    output_field=models.IntegerField(), default=None)
            ),
            bad_meals=Count(
                Case(When(Q(meal__isnull=True) | Q(meal__rating=Meal.BAD), then='meal__rating'),
                    output_field=models.IntegerField(), default=None)
            ),
            indifferent_meals=Count(
                Case(When(Q(meal__isnull=True) | Q(meal__rating=Meal.INDIFFERENT), then='meal__rating'),
                    output_field=models.IntegerField(), default=None)
            )
        )