Django QuerySet annotations with conditions

The Django ORM is a convenient way to extract data from the database, and the annotate() clause that you can use with QuerySets is also a useful way to dynamically generate additional data for each object when the data is being extracted.

Say, for example, we have the following Article and Category models in a blog app:

# blog/models.py
from django.db import models


class Category(models.Model):
    title = models.Charfield(max_length=255)


class Article(models.Model):
    title = models.CharField(max_length=255)
    text = models.CharField(max_length=255)
    category = models.ForeignKey(Category)
    published = models.BooleanField(default=False)
    read_min = models.IntegerField()

Appending the annotate() clause onto a QuerySet lets you add an attribute to each item in the QuerySet, like if you wanted to count the amount of articles in each category. However, sometimes you only want to count objects that match a certain condition, for example only counting articles that are published.

To do this you can use Q objects as filters and pass them into Count in the annotation clause:

from django.db.models import Q
from django.db.models import Count

from blog.models import Category


def get_categories():
    filters = Q(published=True)
    return Category.objects.all().annotate(Count('article', filters))


categories = get_categories()
print(categories[0].article__count)
# 5

Each category item that is returned in the QuerySet should now have an attribute with a default name of article__count. You can change the default name of this attribute by passing Count into annotate as a keyword argument with your desired name instead. For example, you could write annotate(num_articles=Count('article', filters)) if you want the attribute to be called num_articles.

Combining conditions

You can use & and | to create various combinations of Q object conditions, here are some examples:

from django.db.models import Q

# Either condition can be True
or_filters = Q(text__isnull=False) | Q(read_min__gte=1)

# All 3 conditions must be True
and_filters = Q(published=True) & Q(text__isnull=False) & Q(read_min__gte=1)

# First condition must be True, 
# either second or third condition can be True.
complex = Q(published=True) & (Q(read_min__gte=1) | Q(text__isnull=False))

Multiple annotations

Beware that creating multiple annotations on a single QuerySet can yield incorrect calculations. In some instances, using distinct() on the QuerySet can help to remedy this.‌