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()
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.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
You can use
| 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))
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.