CODEWITHSUNDEEP
djangodjango-modelsaggregate-functionsdjango-orm
Yuval Adam
Yuval Adam

Reputation: 165242

Annotate (group) dates by month/year in Django

Using the Django DateQuerySet I'm pulling related years for item objects from a Group query.

>>> Group.objects.all().dates('item__date', 'year')
[datetime.date(1990, 1, 1), datetime.date(1991, 1, 1), ...(remaining elements truncated)...']

Now I want to perform a count by distinct year on these dates. I thought this would work:

>>> Group.objects.all().dates('item__date', 'year').annotate(Count('year'))
FieldError: Cannot resolve keyword 'year' into field.

But looks like I'm missing something. How can I fix this query?

I've also tried this query:

>>> (Group
     .objects
     .all()
     .extra(select=
         {'year': 
          connections[Group.objects.db].ops.date_trunc_sql('year', 'app_item.date')}))
ProgrammingError: missing FROM-clause entry for table "app_item" LINE 1: SELECT (DATE_TRUNC('year', app_item.date)) AS...

But that doesn't work either.

Upvotes: 13

Views: 17338

Answers (2)

mycroft
mycroft

Reputation: 300

For anyone finding this after django 1.9 there is now a TruncDate (TruncMonth, TruncYear) that will do this.

from django.db.models.functions import TruncDate

(Group.objects.all().annotate(date=TruncDate('your_date_attr')
                    .values('date')
                    .annotate(Count('items'))

Hope it helps.

Upvotes: 30

frnhr
frnhr

Reputation: 12903

Try something along these lines:

from django.db.models import Count

Item.objects.all().\
        extra(select={'year': "EXTRACT(year FROM date)"}).\
        values('year').\
        annotate(count_items=Count('date'))

You might want to use item_instance._meta.fields instead of manually specifying "date" in the MySQL statement there...

Also, note that I started with Item QuerySet instead of Group, for the sake of simplicity. It should be possible to either filter the Item QuerySet to get the desired result, or to make the extra bit of MySQL more complicated.

EDIT:

This might work, but I'd definitely test the guts out of it before relying on it :)

Group.objects.all().\
    values('item__date').\
    extra(select={'year': "EXTRACT(year FROM date)"}).\
    values('year').\
    annotate(count=Count('item__date'))

Upvotes: 12

Related Questions