Django ORM Queries - Filter, Exclude, Annotate, Aggregate

Introduction

Django ORM provides powerful query capabilities including filtering, exclusions, annotations, and aggregations. These operations allow you to retrieve and manipulate data efficiently.

Basic Query Operations

from myapp.models import Book, Author, Publisher

# Get all objects
all_books = Book.objects.all()

# Filter with exact match
published_books = Book.objects.filter(status='published')

# Exclude records
draft_books = Book.objects.exclude(status='published')

# Get single object
book = Book.objects.get(id=1)

# Chaining filters
books = Book.objects.filter(
    status='published',
    published_year__gte=2020
).filter(pages__lte=500)

# Field lookups
recent = Book.objects.filter(published_year__gt=2020)
python_books = Book.objects.filter(title__contains='Python')
authors = Author.objects.filter(name__startswith='J')

Annotations and Aggregations

from django.db.models import Count, Sum, Avg, Max, Min, F, Q, Prefetch

# Aggregation
from django.db.models import Count

book_count = Book.objects.count()
total_pages = Book.objects.aggregate(Sum('pages'))
avg_price = Book.objects.aggregate(Avg('price'))

# Annotations
from django.db.models import Count

authors_with_books = Author.objects.annotate(
    book_count=Count('book')
).filter(book_count__gt=5)

# Multiple aggregations
stats = Book.objects.aggregate(
    total_books=Count('id'),
    total_pages=Sum('pages'),
    avg_price=Avg('price'),
    max_pages=Max('pages'),
    min_price=Min('price')
)

# Conditional aggregation
published_stats = Book.objects.filter(
    status='published'
).aggregate(
    count=Count('id'),
    total=Sum('price')
)

Complex Queries with Q Objects

from django.db.models import Q

# OR queries
books = Book.objects.filter(
    Q(status='published') | Q(featured=True)
)

# NOT queries
books = Book.objects.exclude(
    Q(status='draft') & Q(published_year__lt=2020)
)

# Complex conditions
books = Book.objects.filter(
    (Q(status='published') & Q(published_year__gte=2020)) |
    Q(featured=True)
)

# Negation
featured = Book.objects.filter(~Q(status='draft'))

Subqueries and Raw SQL

from django.db.models import Subquery, OuterRef

# Subquery for latest book per author
latest_books = Book.objects.filter(
    author=OuterRef('author')
).order_by('-published_year')

authors = Author.objects.annotate(
    latest_book=Subquery(latest_books.values('title')[:1])
)

# Raw SQL
from django.db import connection

with connection.cursor() as cursor:
    cursor.execute(
        "SELECT * FROM books WHERE status = %s",
        ['published']
    )
    books = cursor.fetchall()

# Raw query with model
books = Book.objects.raw(
    "SELECT * FROM books WHERE published_year >= %s",
    [2020]
)

Prefetch and Select Related

# Select related (SQL JOIN for FK)
books = Book.objects.select_related('author', 'publisher')

# Prefetch related (separate queries for M2M/FK)
authors = Author.objects.prefetch_related('books')

# Prefetch with filter
from django.db.models import Prefetch

authors = Author.objects.prefetch_related(
    Prefetch(
        'books',
        queryset=Book.objects.filter(status='published')
    )
)

# Optimize queries
from django.db.models import Only, Defer

books = Book.objects.only('title', 'author__name').defer('content')

Practice Problems

1. Write a query to find all books published in the last 5 years with more than 300 pages
2. Calculate the total revenue per publisher using aggregation
3. Find authors who have written more than 5 books using annotations
4. Implement a search that filters by title, author, or ISBN using Q objects
5. Optimize a query that fetches books with their authors and reviews