2

How can we achieve the following via the Django 1.5 ORM:

SELECT TO_CHAR(date, 'IW/YYYY') week_year, COUNT(*) FROM entries GROUP BY week_year;

EDIT: cf. Follow up: Count of Group Elements With Joins in Django in case you need a join.

Community
  • 1
  • 1
tbz
  • 197
  • 2
  • 10
  • Start reading this https://docs.djangoproject.com/en/dev/topics/db/aggregation/ and then post here what have you tried. – fasouto Jul 17 '14 at 11:29
  • We have tried annotate and extra to add week_year. But then, count is not allowed to use that annotated/extra field. – tbz Jul 17 '14 at 11:34
  • AFAIK, aggregate reduces the result to a single value. However, here we would like to retrieve the counts for all groups in one query. – tbz Jul 17 '14 at 12:15

1 Answers1

2

I had to do something like this recently.

You need to add your week_year column via Django's extra, then you can use that column in the values method.

...it's not obvious but if you then use annotate Django will GROUP BY all of the fields mentioned in the values clause (as described in the docs here https://docs.djangoproject.com/en/dev/topics/db/aggregation/#values)

So your code should look like:

Entry.objects.extra(select={'week_year': "TO_CHAR(date, 'IW/YYYY')"}).values('week_year').annotate(Count('id'))
Anentropic
  • 32,188
  • 12
  • 99
  • 147
  • Interesting. We tried values as well but we used Count('week_year') not Count('id'). I will come back to you within a minute. – tbz Jul 17 '14 at 13:09
  • 1
    Django ORM has a bunch of corner cases I guess, not always clear what will work and what won't... you should be able to `Count(...)` any field on the model, doesn't have to be in `values`... I guess you want to choose a field like `id` though where every row is sure to have a value for it – Anentropic Jul 17 '14 at 13:19
  • We did Count('week_year') because we thought that is what we count over. – tbz Jul 17 '14 at 13:25
  • 1
    it can be anything that's not NULL for the rows you want to count... it's a shame Django doesn't support `Count('*')` like you'd often do in SQL – Anentropic Jul 17 '14 at 13:43
  • Exactly. That is what we discussed right now. Actually, we only want to count the number of entries in a group; not a specific field. – tbz Jul 17 '14 at 13:51
  • yes, it's the count of non-NULL values for that field for each row that then gets aggregated in the GROUP BY – Anentropic Jul 17 '14 at 13:53