Opened 9 years ago

Closed 9 years ago

Last modified 8 years ago

#26010 closed New feature (invalid)

I need to count objects in a queryset by year and month

Reported by: Mahmood Khan Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: Queryset.extra
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Currently, I am doing something along the lines of:
Item.objects.all().\

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

Change History (3)

comment:1 by Simon Charette, 9 years ago

Component: UncategorizedDatabase layer (models, ORM)
Keywords: Queryset.extra added
Resolution: invalid
Status: newclosed
Version: 1.9master

Hi mahmoodkhan,

You don't extra() to achieve this. There's already a couple of open tickets that you can relate to (#25774 comes to mind) but you should annotate your queryset with a Transform in order to extract the year and months.

If you don't want to write your own Transform subclass you can use the undocumented MonthTransform and YearTransform to annotate your queryset:

from django.db.models.lookups import MonthTransform as Month, YearTransform as Year

Item.objects.annotate(
    year=Year('date'),
    month=Month('date'),
).values('year', 'month').annotate(count=Count('pk'))

Keep in mind that you're relying on a private API (used internally to implement the __month and __year lookups) that might be moved around or replaced.

Once values() allows transforms and lookups you should be able to simply do this:

Item.objects.values('date__year', 'date__month').annotate(count=Count('pk'))

in reply to:  1 comment:2 by Mahmood Khan, 9 years ago

Thanks! much appreciated.

Replying to charettes:

Hi mahmoodkhan,

You don't extra() to achieve this. There's already a couple of open tickets that you can relate to (#25774 comes to mind) but you should annotate your queryset with a Transform in order to extract the year and months.

If you don't want to write your own Transform subclass you can use the undocumented MonthTransform and YearTransform to annotate your queryset:

from django.db.models.lookups import MonthTransform as Month, YearTransform as Year

Item.objects.annotate(
    year=Year('date'),
    month=Month('date'),
).values('year', 'month').annotate(count=Count('pk'))

Keep in mind that you're relying on a private API (used internally to implement the __month and __year lookups) that might be moved around or replaced.

Once values() allows transforms and lookups you should be able to simply do this:

Item.objects.values('date__year', 'date__month').annotate(count=Count('pk'))

comment:3 by Rich Rauenzahn, 8 years ago

FYI:

MonthTransform and YearTransform are gone now in Django 1.10.

You can use TruncMonth/TrunchYear from django.db.models.functions.

I believe it is slightly different -- I had to modify my html template to filter the value through |date: 'Y' and |date: 'm', otherwise I got something like "2016, midnight" rather than "2016"

See:

http://stackoverflow.com/questions/8746014/django-group-by-date-day-month-year
https://code.djangoproject.com/ticket/26649
https://docs.djangoproject.com/en/1.10/ref/models/database-functions/#trunc

Note: See TracTickets for help on using tickets.
Back to Top