[Answer]-Django query with annotated conditional expression uses INNER JOIN. How do I get it to use OUTER JOIN?

by

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:

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)
            )
        )
👤Mike Drawback

Leave a comment