Pick a Count 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 answers from this questionnaire, grouped by month, and count the answers "yes" and "no."

For example, I have answers 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 query set to do something like:

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

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

I am looking for a Python way to do this, preferably with a query in django, and not with a dictionary.

Answer 1

First, we need to extract the month and year values ​​for use in our query. We do this using extra(). Unfortunately, Django has no built-in support.

values().

, yes/no annotate() :

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()),
        )
    )

order_by('year', 'month'), , .

:

[{'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},
 ...]

, year month , . , :

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

Answer 2

,

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

result = Questionnaire.objects.extra(
     select={'year': "EXTRACT(year FROM register)",
             'month': "EXTRACT(month FROM register)",
             'yes': "satisfaction = 1",
             'no': "satisfaction = 0",
            }).annotate(yes_count=models.Sum("yes"),
                        no_count=models.Sum("no")).order_by("year", "month").values("yes_count", "no_count", "month", "year")
for row in result:
    print("yes {yes_count}, no {no_count}".format(**row))
    print("date {year}-{month}-01".format(**row))

, , , , .