Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 750
Yuval Adam Avatar asked Feb 17 '14 19:02

Yuval Adam


2 Answers

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.

like image 163
mycroft Avatar answered Nov 18 '22 08:11

mycroft


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'))
like image 35
frnhr Avatar answered Nov 18 '22 10:11

frnhr