0

I need to group and count items by date. I got it on sqlite with the followint:

Books.objects.filter(state="new").extra({"published_at": "date(published_at)"}).values("published_at").annotate(counter=Count("pk"))

But the use of extra() with sql statement cannot be portable on other dbms. For example the above doesn't work on mysql. How i can have a working query for sqlite, postgresql and mysql?

tapioco123
  • 3,235
  • 10
  • 36
  • 42
  • How are you counting by date. It seems you are counting by pk which always will be 1. Also why do you need `"date(published_at)"`. Django's `Datetime` field already gives a datetime object so no need to convert to a date again. – miki725 Sep 30 '13 at 22:31
  • Because published_at is a timestamp and i need to count Books by day. – tapioco123 Oct 01 '13 at 08:02

1 Answers1

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.

Community
  • 1
  • 1
allcaps
  • 10,945
  • 1
  • 33
  • 54
  • Updated my answer. But I'm still not sure if I completely understand what try to achieve. Please elaborate. – allcaps Oct 01 '13 at 08:18
  • What does the SQLite .extra({"published_at": "date(published_at)"}) do? Convert a string to a date? – allcaps Oct 01 '13 at 08:29
  • yes, it converts to date. as i said i need the counter of books published per day – tapioco123 Oct 01 '13 at 08:31
  • This code gets the following error: Join on field 'published_at' not permitted. Did you misspell 'day' for the lookup type? – tapioco123 Oct 01 '13 at 08:33