Django filter by conditional annotation
Is it possible to have "conditional" annotation in Django? Let's say the following models exist
class Author(models.Model):
name = models.CharField()
class Article(models.Model):
title = models.CharField()
published = models.BooleanField()
now I would like to select a limited (filtered) set of authors requests and annotate them both with the total number of books and the published number of books for subsequent use (for example, applying filters in the authors request or ordering it). Something like
Author.objects.filter(name__icontains = 'a').annotate(total_books = Count('books')).annotate(published_books = Count('books', <here published=true filter>))
Is it possible?
source to share
Django ORM invokes translation to SQL. If this cannot be done with a SQL query, it usually means that you cannot do it with an ORM. What you are asking is a different suggestion where
(unless I am missing something or a misunderstanding), requiring a different request. This gives you 2 options:
- Run 2 different queries (with different arguments
filter()
) - If this is an expensive query that you don't want to run twice, you can simply pull the data from the database once and preform the aggregate in normal python code.
source to share
Just a simple thought, you can try:
Author.objects.filter(name__icontains = 'a').\
annotate(total_books = Count('books')).\
annotate(published_books = Count('books__published'))
Since it is BooleanField
written as an integer 0/1, it can do the job. Or it may not be :) Just try it in your code.
source to share