#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)
follow-up: 2 comment:1 by , 9 years ago
Component: | Uncategorized → Database layer (models, ORM) |
---|---|
Keywords: | Queryset.extra added |
Resolution: | → invalid |
Status: | new → closed |
Version: | 1.9 → master |
comment:2 by , 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
andYearTransform
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 , 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
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
andYearTransform
to annotate your queryset: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: