Geek Answers Handbook

How to make this GROUP BY query in Django ORM with annotation and aggregate

I really didn’t think about how to translate to GROUP BYboth HAVINGDjango QuerySet.annotateand QuerySet.aggregate. I am trying to translate this SQL query into ORM by saying

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 work on several different database systems, so I try to stay away from pure SQL as much as possible.

+5
source share
2 answers

I think that for this in one request you can have a month and a year as separate fields ...

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

group by by pub_date. extract inline.

:

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()

:

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

, pub_year pub_month save() pub_date.

Edit:

- extra; ...

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

, (), extra, - . , SQL Server year(pub_date) extract(year from pub_date)...

, , , .

+14

More articles:


All Articles