Django QuerySet Annotate

Key annotate() Features

books = Book.objects.annotate(
    num_reviews=Count('reviews')
)

books = Book.objects.annotate(
    num_reviews=Count('reviews')
).filter(num_reviews__gt=10)

authors = Author.objects.annotate(
    avg_rating=Avg('books__rating')
)

authors = Author.objects.annotate(
    book_count=Count('books')
)

🔹 Basic annotate() Usage

Use annotate() to add calculated fields to each object in your QuerySet. The new field becomes accessible as an attribute on each object, just like regular model fields.

# models.py
from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name='books')
    price = models.DecimalField(max_digits=6, decimal_places=2)

class Review(models.Model):
    book = models.ForeignKey(Book, on_delete=models.CASCADE, related_name='reviews')
    rating = models.IntegerField()

# views.py
from django.db.models import Count
from myapp.models import Book

# Add review count to each book
books = Book.objects.annotate(review_count=Count('reviews'))

for book in books:
    print(f"{book.title}: {book.review_count} reviews")

🔹 Count with annotate()

Count() is commonly used with annotate() to count related objects. This is perfect for showing how many reviews a book has, how many books an author wrote, or similar relationships.

from django.db.models import Count
from myapp.models import Author, Book

# Count books per author
authors = Author.objects.annotate(book_count=Count('books'))

for author in authors:
    print(f"{author.name}: {author.book_count} books")

# Count reviews per book
books = Book.objects.annotate(
    review_count=Count('reviews')
).order_by('-review_count')

# Filter by annotated field
popular_books = Book.objects.annotate(
    review_count=Count('reviews')
).filter(review_count__gte=10)

print(f"Popular books: {popular_books.count()}")

🔹 Sum with annotate()

Use Sum() with annotate() to calculate totals for each object. This is useful for calculating total revenue per author, total pages per category, or any cumulative values.

from django.db.models import Sum
from myapp.models import Author

# Calculate total pages written by each author
authors = Author.objects.annotate(
    total_pages=Sum('books__pages')
)

for author in authors:
    print(f"{author.name}: {author.total_pages} total pages")

# Calculate total revenue per author
authors = Author.objects.annotate(
    total_revenue=Sum('books__price')
)

# Find most prolific authors
top_authors = Author.objects.annotate(
    total_pages=Sum('books__pages')
).order_by('-total_pages')[:5]

for author in top_authors:
    print(f"{author.name}: {author.total_pages} pages")

🔹 Avg with annotate()

Use Avg() with annotate() to calculate average values for each object. Perfect for finding average ratings per book, average price per author, or mean values across relationships.

from django.db.models import Avg
from myapp.models import Book, Author

# Calculate average rating for each book
books = Book.objects.annotate(
    avg_rating=Avg('reviews__rating')
)

for book in books:
    print(f"{book.title}: {book.avg_rating:.1f} stars")

# Calculate average book price per author
authors = Author.objects.annotate(
    avg_book_price=Avg('books__price')
)

# Find highly rated books
top_rated = Book.objects.annotate(
    avg_rating=Avg('reviews__rating')
).filter(avg_rating__gte=4.5).order_by('-avg_rating')

for book in top_rated:
    print(f"{book.title}: {book.avg_rating:.1f} stars")

🔹 Multiple Annotations

You can add multiple annotated fields in a single query. This is efficient and lets you calculate several metrics at once for each object in your QuerySet.

from django.db.models import Count, Avg, Sum
from myapp.models import Author

# Multiple annotations at once
authors = Author.objects.annotate(
    book_count=Count('books'),
    avg_rating=Avg('books__reviews__rating'),
    total_pages=Sum('books__pages'),
    total_revenue=Sum('books__price')
)

for author in authors:
    print(f"{author.name}:")
    print(f"  Books: {author.book_count}")
    print(f"  Avg Rating: {author.avg_rating:.1f}")
    print(f"  Total Pages: {author.total_pages}")
    print(f"  Revenue: ${author.total_revenue}")

🔹 Filtering on Annotations

After annotating, you can filter based on the calculated values. This powerful technique lets you find objects that meet criteria based on aggregated data from related objects.

from django.db.models import Count, Avg
from myapp.models import Author, Book

# Find authors with more than 3 books
prolific_authors = Author.objects.annotate(
    book_count=Count('books')
).filter(book_count__gt=3)

# Find books with high average ratings
highly_rated = Book.objects.annotate(
    avg_rating=Avg('reviews__rating')
).filter(avg_rating__gte=4.5)

# Complex filtering
popular_authors = Author.objects.annotate(
    book_count=Count('books'),
    avg_rating=Avg('books__reviews__rating')
).filter(
    book_count__gte=3,
    avg_rating__gte=4.0
)

for author in popular_authors:
    print(f"{author.name}: {author.book_count} books, {author.avg_rating:.1f} rating")

🔹 annotate() vs aggregate()

Understanding the difference between annotate() and aggregate() is crucial. annotate() adds fields to each object and returns a QuerySet, while aggregate() calculates a single value across all objects and returns a dictionary.

from django.db.models import Count, Avg
from myapp.models import Book

# annotate() - adds field to each object
books_with_counts = Book.objects.annotate(
    review_count=Count('reviews')
)
# Returns QuerySet, can iterate
for book in books_with_counts:
    print(f"{book.title}: {book.review_count}")

# aggregate() - single value for all objects
total_stats = Book.objects.aggregate(
    total_reviews=Count('reviews'),
    avg_rating=Avg('reviews__rating')
)
# Returns dictionary
print(f"Total reviews: {total_stats['total_reviews']}")
print(f"Overall avg: {total_stats['avg_rating']:.1f}")