[Answer]-Django: grouping and counting by day

by

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.

👤allcaps

Leave a comment