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

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

Django provides a count() method for precisely this reason. ... queries (for example, queries with OR statements), you can use Q objects . ... Each argument to annotate() is an annotation that will be added to each object in the QuerySet that is ... to make sure the DISTINCT ON expressions match those at the beginning of the ...

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

We’ll take the example of Authors and Books. In 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 idauthor_name and timestamps.

class Book(models.Model): 
        ('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:

    def __str__(self):
        return self.book_name

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

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

Note: Mysql File link is given below. Download the test data we have used in this post.


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



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}

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

django framework python django learn python

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Python Django Tutorial | Django Course

🔥Intellipaat Django course: 👉This Python Django tutorial will help you learn what is django web development &...

Django Crud: Getting Started with Django Framework

Django: It is a python web framework. We only perform the basic create, read, update and delete operation in this example.

Learning Python: The Prompt, Then Read Template

The most Python programs will consist of three steps — getting input into the program, processing the input in some way, and outputting the results of the processing. I’m going to focus on one part of that step — getting input into a program — by prompting the user to enter some data and then reading the data into the program. This is a mostly straightforward process except for some data conversions that have to occur when you are inputting numbers.

Main Pros and Cons of Django As A Web Framework for Python Developers

India's best Institute for Django Online Training Course & Certification. Django is a high-level Python Web framework that encourages rapid development and clean, pragmatic design.

Python Tricks Every Developer Should Know

In this tutorial, you’re going to learn a variety of Python tricks that you can use to write your Python code in a more readable and efficient way like a pro.