1👍
If you want a database agnostic query, than don’t use extra().
books = Books.objects.filter(state=”new”)
.order_by(‘published_at’, )
.values(“published_at”, )
.annotate(count=Count(“published_at__day”))
Accessing a date__day (published_at__day) fails with:
FieldError: Join on field ‘date’ not permitted. Did you misspell ‘day’
for the lookup type?
If rewrote Django annotate groupings by month to grouping by day.
Book.objects.all().extra(select={'day': 'extract( day from date )'}).values('day').annotate(num=Count('date')).order_by()
You may want to replace .all() with a .filter(year=some_year) because this will span mutiple years.
Extract
is confirmed to work on MySQL and PostgreSQL.
Source:stackexchange.com