How to do this GROUP BY query in Django's ORM with annotate and aggregate

Question

I don't really have groked how to translate GROUP BY and HAVING to Django's QuerySet.annotate and QuerySet.aggregate. I'm trying to translate this SQL query into ORM speak

SELECT EXTRACT(year FROM pub_date) as year, EXTRACT(month from pub_date) as month, COUNT(*) as article_count FROM articles_article GROUP BY year,month;

which outputs this:

[(2008.0, 10.0, 1L), # year, month, number of articles
(2009.0, 2.0, 1L),
(2009.0, 7.0, 1L),
(2008.0, 5.0, 3L),
(2008.0, 9.0, 1L),
(2008.0, 7.0, 1L),
(2009.0, 5.0, 1L),
(2008.0, 8.0, 1L),
(2009.0, 12.0, 2L),
(2009.0, 3.0, 1L),
(2007.0, 12.0, 1L),
(2008.0, 6.0, 1L),
(2009.0, 4.0, 2L),
(2008.0, 3.0, 1L)]

My Django model:

class Article(models.Model):
    title = models.CharField(max_length=150, verbose_name=_("title"))
    # ... more 
    pub_date = models.DateTimeField(verbose_name=_('publishing date'))

This project should run on a couple of different DB systems, so I'm trying to stay away from pure SQL as much as possible.

Answer 1

I think to do it in one query you might have to have month and year as separate fields...

Article.objects.values('pub_date').annotate(article_count=Count('title'))

That would group by by pub_date. But there is no way I can think of to do the equivalent of the extract function clause inline there.

If your model were:

class Article(models.Model):
    title = models.CharField(max_length=150, verbose_name=_("title"))
    # ... more 
    pub_date = models.DateTimeField(verbose_name=_('publishing date'))
    pub_year = models.IntegerField()
    pub_month = models.IntegerField()

Then you could do:

Article.objects.values('pub_year', 'pub_month').annotate(article_count=Count('title'))

If you are going to do this, I would recommend having pub_year and pub_month be automatically populated by overriding the save() method for Article and extracting the values from pub_date.

---

Edit:

One way to do it is to use extra; but it won't grant you database independence...

models.Issue.objects.extra(select={'year': "EXTRACT(year FROM pub_date)", 'month': "EXTRACT(month from pub_date)"}).values('year', 'month').annotate(Count('title'))

While this will work, I think (untested), it will require you to modify the extra fields if you ever change database servers. For instance, in SQL Server you would do year(pub_date) instead of extract(year from pub_date)...

This might not be so bad if you come up with a custom model manager that you prominently tag as requiring such database engine dependent changes.

Answer 2

You can make an extract with dates: http://docs.djangoproject.com/en/dev/ref/models/querysets/#dates-field-kind-order-asc