Django and conditional aggregates

Question

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?

Answer 1

I think this should do the trick:

from django.db.models import Count
Author.objects.filter(article__pubdate__isnull=False).annotate(Count('article'))

Answer 2

I solved my problem by creating a SQL view with the needed GROUP BY statement, and a model for said view with managed = False and a OneToOneField to the Author table. It's not the most efficient or elegant solution, but it works alright.