我真的不知道如何翻译GROUP BY
and HAVING
到姜戈的QuerySet.annotate
and QuerySet.aggregate
。我正在尝试将这个 SQL 查询转换为 ORM 语言
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;
输出如下:
[(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)]
我的姜戈模型:
class Article(models.Model):
title = models.CharField(max_length=150, verbose_name=_("title"))
# ... more
pub_date = models.DateTimeField(verbose_name=_('publishing date'))
这个项目应该在几个不同的数据库系统上运行,所以我试图尽可能远离纯 SQL。