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 the authors and annotate them with my corresponding number of articles. This is a piece of cake with Django aggregates . The problem is that it should only consider articles that have already been published. According to ticket 11305 in the Django tracker guide, this is not yet possible. I tried to use the CountIf annotation mentioned on this ticket, but it does not quote the datetime string and does not make all the connections it needs.

So what is the best solution besides writing your own SQL?

Answer 1

Django 1.8+ Solution

Since Django 1.8, conditional expressions are available for creating queries.

See the documentation for more information, but a quick solution to your question would look something like this:

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

I have not tested it, but it should work.

Answer 2

You can use the django-aggregate-if application inspired by ticket 11305 . Or you can simply use the extra method for a set of queries (suppose your application is called "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'}) 

Answer 3

I solved my problem by creating an SQL view with the necessary GROUP BY statement and a model for the specified view with managed = False and a OneToOneField in the Author table. This is not the most efficient or elegant solution, but it works well.