Django query - "case when" with aggregation function

Question

I have the following django model (mapped to table 'A'):

class A(models.Model):
    name = models.CharField(max_length=64, null=False)
    value = models.IntegerField()
    ...

I want to perform the following simple query on top:

select avg(case 
        when (value > 0 and value <= 50) then 0 
        when (value > 50 and value < 70) then 50 
        else 100 end) 
from A
where ...

I'm trying to avoid raw SQL - How can this be implemented with django (in the above example I'm using avg, but the same question is also relevant for max, min, sum etc.)?

I tried using extra and aggregate:

extra(select={'avg_field': case_when_query})

and

aggregate(Avg('avg_field')), 

but the aggregate function only works with model fields so the extra field cannot be used here. How can this be done with django?

Thanks for the help

Answer 1

As far as I know there is (unfortunately) no way to do what you described without resorting to raw SQL.

That said there is a way to calculate the average the way you describe if you are willing to denormalize your data a bit. For instance you can add a new column called average_field that is automatically set to the appropriate value on save(). You can either override save() or tap a signal to do this automatically. For e.g.

class A(models.Model):
    name = models.CharField(max_length=64, null=False)
    value = models.IntegerField()
    average_field = models.IntegerField(default = 0)

    def _get_average_field(self):
        # Trying to match the case statement's syntax.
        # You can also do 0 < self.value <= 50
        if self.value > 0 and self.value <= 50:
            return 0
        elif self.value > 50 and self.value < 70:
            return 50
        else:
            return 100

    def save(self, *args, **kwargs):
        if self.value:
            self.average_field = self._get_average_field()
        super(A, self).save(*args, **kwargs)

Once you do this your querying becomes very easy.

A.objects.filter(...).aggregate(avg = Avg('average_field'))

Answer 2

What can be done that will still allow us to use django queryset is something like this:

qs = A.objects.extra(select={"avg_field": 
                     "avg(case when...)"}).filter(...).values("avg_field")

To use the result:

qs[0]["avg_filed"]

And this would allow the needed functionality.