Django和条件聚合 [英] Django and conditional aggregates

问题描述

我有两个模型，作者和文章：

I have two models, authors and articles:

class Author(models.Model):
    name = models.CharField('name', max_length=100)

class Article(models.Model)
    title = models.CharField('title', max_length=100)
    pubdate = models.DateTimeField('publication date')
    authors = models.ManyToManyField(Author)

现在我想选择所有作者，并用他们各自的文章数量对它们进行注释。这是Django的聚合的蛋糕。问题是，它只应该计算已经发布的文章。根据Django售票追踪器中的 ticket 11305 ，这还不可能。我试图使用该票中提到的 CountIf 注释，但它不引用datetime字符串，并不会使所有连接都需要。

Now I want to select all authors and annotate them with their respective article count. That's a piece of cake with Django's aggregates. Problem is, it should only count the articles that are already published. According to ticket 11305 in the Django ticket tracker, this is not yet possible. I tried to use the CountIf annotation mentioned in that ticket, but it doesn't quote the datetime string and doesn't make all the joins it would need.

所以，除了编写自定义SQL之外，最好的解决方案是什么？

So, what's the best solution, other than writing custom SQL?

推荐答案

Django 1.8+解决方案

由于Django 1.8，条件表达式可用于构建查询集。

Django 1.8+ solution

Since Django 1.8, conditional expressions are available for building querysets.

有关更多详细信息，请参阅文档，但是您的问题的快速解决方案将会如下：

For more details consult the documentation, but a fast solution for your question would look something like:

today = datetime.date.today()
authors = Author.objects.all().annotate(article_count=Sum(
    Case(When(articles__pubdate__lt=today, then=1),
         output_field=IntegerField())
))

我没有检查，但它应该工作。

I didn't check it though, but it should work.

这篇关于Django和条件聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！