aldesabido aldesabido - 5 months ago 122
Python Question

Django: Query Group By Month

How to calculate total by month without using extra?

I'm currently using:


  • django 1.8

  • postgre 9.3.13

  • Python 2.7



Example.

enter image description here

What I have tried so far.

#Doesn't work for me but I don't mind because I don't want to use extra
truncate_month = connection.ops.date_trunc_sql('month','day')
invoices = Invoice.objects.filter(is_deleted = False,company = company).extra({'month': truncate_month}).values('month').annotate(Sum('total'))

----
#It works but I think that it's too slow if I query a big set of data
for current_month in range(1,13):
Invoice.objects.filter(date__month = current__month).annotate(total = Sum("total"))


P.S. I know that this question is already asked multiple times but I don't see any answer.

Thanks!




SOLUTION:

Thanks to @Vin-G's answer.

enter image description here

Answer

First, you have to make a Function that can extract the month for you:

from django.db import models
from django.db.models import Func

class Month(Func):
    function = 'EXTRACT'
    template = '%(function)s(MONTH from %(expressions)s)'
    output_field = models.IntegerField()

After that all you need to do is

  1. annotate each row with the month
  2. group the results by the annotated month using values()
  3. annotate each result with the aggregated sum of the totals using Sum()

i.e.

from django.db.models import Sum

summary = (Invoice.objects
              .annotate(m=Month('date'))
              .values('m')
              .annotate(total=Sum('total'))

See the full gist here: https://gist.github.com/alvingonzales/ff9333e39d221981e5fc4cd6cdafdd17

Now i'm not sure if the syntax for the EXTRACT postgresql is correct since I don't have a postgresql server to test with for the moment, but it should work with a bit of tweaking on your end.

If you need further information:

Details on creating your own Func classes: https://docs.djangoproject.com/en/1.8/ref/models/expressions/#func-expressions

Details on the values() clause, (pay attention to how it interacts with annotate() with respect to order of the clauses): https://docs.djangoproject.com/en/1.9/topics/db/aggregation/#values

the order in which annotate() and values() clauses are applied to a query is significant. If the values() clause precedes the annotate(), the annotation will be computed using the grouping described by the values() clause.