Count choices in django

Question

I have a model like:

class Questionnaire(models.Model):
    YES_NO_CHOICES = (
        (True, 'Yes'),
        (False, 'No'),
    )
    satisfaction = models.BooleanField(choices=YES_NO_CHOICES, default=True)
    register = models.DateField(auto_now_add=True)

I need to get responses from this questionnaire grouped by months and count "yes" and "no" responses.

Example, I have responses like this:

{
    '2015-11-29': {True: 1, False: 2},
    '2015-11-30': {True: 3, False: 1},
    '2015-12-01': {True: 5, False: 2},
    '2015-12-05': {True: 3, False: 6}
}

I need a django queryset to do something like:

{
    {'2015-11-01': {True: 4, False: 3},
    {'2015-12-01': {True: 8, False: 8}
}

The date is not important, in template I'll just use the month value (01, 02, 03, ..., 11, 12).

I'm searching for a pythonic way to do this, preferably with queryset in django, not dictionary.

Answer 1

First, we need to extract the month and year values for use in our query. We do this by using extra(). Django has no builtin support for that, unfortunately.

Then, we can group by year and month using values().

Finally, we can aggregate on the yes/no answers by using annotate() and conditional expressions:

from django.db import connections
from django.db.models import Case, IntegerField, Sum, When

conn = connections[Questionnaire.objects.db]

Questionnaire.objects.extra(
    select={
        'year': conn.ops.date_trunc_sql('year', 'register'),
        'month': conn.ops.date_trunc_sql('month', 'register'),
    }).values(
        'year', 'month'
    ).annotate(
        yes_count=Sum(
            Case(When(satisfaction=True, then=1),
                 output_field=IntegerField()),
        ),
        no_count=Sum(
            Case(When(satisfaction=False, then=1),
                 output_field=IntegerField()),
        )
    )

You might also want to order_by('year', 'month'), but that is not necessary for the code to work.

The result will be a list of dictionaries like this:

[{'year': '2015-01-01', 'month': '2015-11-01', 'yes_count': 201, 'no_count': 422},
 {'year': '2015-01-01', 'month': '2015-12-01', 'yes_count': 324, 'no_count': 223},
 ...]

As you can see, year and month are not numeric, but are strings. However you can easily extract the year and the month by splitting:

year = int(item['year'].split('-')[0])
month = int(item['month'].split('-')[1])