Annotate (group) dates by month/year in Django

aggregate-functions, django, django-models, django-orm

Solution

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'))

Problem

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.

Original source

Related problems