Django and conditional aggregates

aggregate, django, python

Solution

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.

Problem

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) ``` 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. So, what's the best solution, other than writing custom SQL?

Original source