What is annotation in Django?

In general terms, annotations can be defined as commenting or adding notes with appropriate message/text. In Django, annotations are
used to add additional columns to queryset objects while querying. Consider a situation where you need to retrieve a list of hotel rooms and with average ratings to each of those rooms there in that situation we use annotate() the method provided by Django.

Table of Contents

• Preparing Models
• Examples
  • Count books are written by Author
  • Count books Published by Author
  • Count books Published by Author and order them by highest
  • Count books Published and kept hold by Author
  • Using Reverse relationship count books published and on hold
  • Filter authors who have not wrote a single book
• Conclusion

Preparing Models

We’ll take the example of Authors and Books. In models.py file create Author and Book model.

from django.db import models

class Author(models.Model):
    id = models.AutoField(primary_key=True)
    author_name = models.CharField(max_length=255)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "authors"
        verbose_name = "Author"
        verbose_name_plural = "Authors"

    def __str__(self):
        return self.author_name

Author the model has a primary key id, author_name and timestamps.

class Book(models.Model): 
    BOOK_STATUS=(
        ('PUBLISHED', 'Published'),
        ('ON_HOLD', 'On Hold'), 
    )
    id = models.AutoField(primary_key=True)
    book_name = models.CharField(max_length=255) 
    author = models.ForeignKey('Author',on_delete=models.CASCADE,related_name='author')
    status = models.CharField(max_length=255, choices = BOOK_STATUS)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table="books"
        verbose_name="Book"
        verbose_name_plural="Books"

    def __str__(self):
        return self.book_name

Book the model has the primary key as id, book_name, author which is mapped to the author model, status which has choices”PUBLISHED” and “ON_HOLD”.

• Create migrations for models using a command python manage.py makemigrations <app_name>
• After migrations to create tables for models use a command python manage.py migrate.
• Add some data to the author and book model so that we can perform queryset operations.

Examples

Count books are written by Author

In this example, we’ll query Book model and retrieve the count of books written by each author.

from my_app.models import (Book, Author, )
from django.db.models import Count, Avg, Q

Book.objects.values('author').annotate(num_books=Count('book_name'))

#---------------------------------------------------------------------#
#OUTPUT
#---------------------------------------------------------------------#

Below is the sql query generated
SELECT `books`.`author_id`, COUNT(`books`.`book_name`) AS `num_books` 
FROM `books` 
GROUP BY `books`.`author_id` ORDER BY NULL

Queryset list
<QuerySet [
            {'num_books': 4, 'author': 1}, {'num_books': 3, 'author': 2},
            {'num_books': 2, 'author': 3}, {'num_books': 6, 'author': 4},
            {'num_books': 7, 'author': 5}, {'num_books': 6, 'author': 6},
            {'num_books': 4, 'author': 7}
]></QuerySet>        

The annotate() the method generates count clause and it is grouped by author.id given in values('author') method.

Count books Published by Author

Book.objects.values('author').annotate(num_books=Count('book_name')).filter(status="PUBLISHED")

#---------------------------------------------------------------------#
#OUTPUT
#---------------------------------------------------------------------#
SELECT `books`.`author_id`, COUNT(`books`.`book_name`) AS `num_books` 
FROM `books` 
WHERE `books`.`status` = 'PUBLISHED'
GROUP BY `books`.`author_id` 
ORDER BY NULL

<QuerySet [
    {'num_books': 3, 'author': 1}, {'num_books': 2, 'author': 2}, {'num_books': 1, 'author': 3}, 
    {'num_books': 2, 'author': 4}, {'num_books': 4, 'author': 5}, {'num_books': 3, 'author': 6}, 
    {'num_books': 3, 'author': 7}
]></QuerySet>

The filter() method filters queryset and return only those authors count who has books published.

Count books Published by Author and order them by highest

Book.objects.values('author').annotate(num_books=Count('book_name')).filter(status="PUBLISHED").order_by('-num_books')

This query orders field num_books in the highest order.

Count books Published and kept hold by Author

books = Book.objects.values('author').annotate(
    no_of_books_published=Count('id', filter=Q(status="PUBLISHED")),
    no_of_books_on_hold=Count('id', filter=Q(status="ON_HOLD")),
).order_by('-no_of_books_published')

print(books.query)
print(books)

print("Author | Published | On Hold")
for book in books:
    print("{} | {} | {}".format(book['author'], book.get('no_of_books_published'), book.get('no_of_books_on_hold')))

#---------------------------------------------------------------------#
#OUTPUT
#---------------------------------------------------------------------#
SELECT `books`.`author_id`, 
COUNT(CASE WHEN `books`.`status` = ON_HOLD THEN `books`.`id` ELSE NULL END) AS `no_of_books_on_hold`, 
COUNT(CASE WHEN `books`.`status` = PUBLISHED THEN `books`.`id` ELSE NULL END) AS `no_of_books_published` 
FROM `books` 
GROUP BY `books`.`author_id` 
ORDER BY `no_of_books_published` DESC

<QuerySet [
    {'no_of_books_published': 4, 'no_of_books_on_hold': 0, 'author': 1}, {'no_of_books_published': 4, 'no_of_books_on_hold': 3, 'author': 5},
    {'no_of_books_published': 3, 'no_of_books_on_hold': 3, 'author': 6}, {'no_of_books_published': 3, 'no_of_books_on_hold': 1, 'author': 7}, 
    {'no_of_books_published': 2, 'no_of_books_on_hold': 1, 'author': 2}, {'no_of_books_published': 2, 'no_of_books_on_hold': 4, 'author': 4}, 
    {'no_of_books_published': 1, 'no_of_books_on_hold': 1, 'author': 3}
]>
Author | Published | On Hold
    1    |     4     |   0
    5    |     4     |   3
    6    |     3     |   3
    7    |     3     |   1
    2    |     2     |   1
    4    |     2     |   4
    3    |     1     |   1

Using annotate() the method you can create multiple fields/columns in query and also filter them using Q objects.

Using Reverse relationship count books published and on hold

Till now we have queried to Book model. But we can also do that in a reverse relationship. In this case Book model has a foreign key author which is mapped to Author model and Author model is in reverse relationship with Book model.

authors = Author.objects.annotate(
    no_of_pub = Count('id', filter=Q(author__status="PUBLISHED")),
    no_of_books_on_hold = Count('id', filter=Q(author__status="ON_HOLD"))
).filter(no_of_pub__gt=0).order_by('-no_of_pub')

print(authors.query)

print("Author | Published | On Hold")
for author in authors:
    print("{} | {} | {}".format(author.id, author.no_of_pub, author.no_of_books_on_hold))

#---------------------------------------------------------------------#
#OUTPUT
#---------------------------------------------------------------------#
SELECT `authors`.`id`, `authors`.`author_name`, `authors`.`created_at`, `authors`.`updated_at`, 
COUNT(CASE WHEN `books`.`status` = PUBLISHED THEN `authors`.`id` ELSE NULL END) AS `no_of_pub`, 
COUNT(CASE WHEN `books`.`status` = ON_HOLD THEN `authors`.`id` ELSE NULL END) AS `no_of_books_on_hold` 
FROM `authors` 
LEFT OUTER JOIN `books` 
ON (`authors`.`id` = `books`.`author_id`) 
GROUP BY `authors`.`id` HAVING 
COUNT(CASE WHEN (`books`.`status` = PUBLISHED) 
THEN 
`authors`.`id` ELSE NULL END) > 0 ORDER BY `no_of_pub` DESC
Author | Published | On Hold
    5    |     4     |   3
    1    |     4     |   0
    6    |     3     |   3
    7    |     3     |   1
    4    |     2     |   4
    2    |     2     |   1
    3    |     1     |   1

As you can see we have replaced model Book as Author and the output is the same.

Filter authors who have not written a single book

authors = Author.objects.annotate(
    no_of_pub = Count('id', filter=Q(author__status="PUBLISHED")),
    no_of_books_on_hold = Count('id', filter=Q(author__status="ON_HOLD"))
).filter(no_of_pub=0,no_of_books_on_hold=0).order_by('-no_of_pub')

print(authors.query)

print("Author | Published | On Hold")
for author in authors:
    print("{} | {} | {}".format(author.id, author.no_of_pub, author.no_of_books_on_hold))

#---------------------------------------------------------------------#
#OUTPUT
#---------------------------------------------------------------------#
SELECT `authors`.`id`, `authors`.`author_name`, `authors`.`created_at`, `authors`.`updated_at`, 
COUNT(CASE WHEN `books`.`status` = PUBLISHED THEN `authors`.`id` ELSE NULL END) AS `no_of_pub`, 
COUNT(CASE WHEN `books`.`status` = ON_HOLD THEN `authors`.`id` ELSE NULL END) AS `no_of_books_on_hold` 
FROM `authors`
LEFT OUTER JOIN `books` ON (`authors`.`id` = `books`.`author_id`) 
GROUP BY `authors`.`id` 
HAVING (COUNT(CASE WHEN (`books`.`status` = ON_HOLD) THEN `authors`.`id` ELSE NULL END) = 0 AND COUNT(CASE WHEN (`books`.`status` = PUBLISHED) THEN `authors`.`id` ELSE NULL END) = 0) 
ORDER BY `no_of_pub` DESC

Author | Published | On Hold
    8   |    0      |   0
    9   |    0      |   0

The above query does a left outer join and returns only those authors who have not written any book.

Conclusion

We have come to the final part of our post “How to use Django Annotate(), Count() and Q() Objects for Beginners”. we want to thank you for reading our post. If you like this share post and don’t forget to comment.

Related Posts

• Django Pagination
• Django Creating Custom User Model with Custom Authentication Backend

Summary

Reviewer

The Code Learners

Review Date

2020-10-23

Reviewed Item

How to use Django Annotate(), Count() and Q() Objects for Beginners

Author Rating

5

Software Name

Django Framework

Software Name

Windows Os, Mac Os, Ubuntu Os

Software Category

Web Development