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('pub开发者_如何学编程lication 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?

You could use django-aggregate-if application, that inspired by ticket 11305. Or you can just use the extra method for queryset (suppose your application named "articles"):

Author.objects.all().extra(
    select={'article_count': 'SELECT COUNT(*) FROM "articles_article" '
                             'INNER JOIN "articles_article_authors" '
                             'ON "articles_article"."id" = '
                             '   "articles_article_authors"."article_id" '
                             'WHERE "articles_article_authors"."author_id" = '
                             '      "articles_author"."id" '
                             'AND "articles_article"."pubdate" IS NOT NULL'})

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.

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.