Django ORM queries you will use daily: a practical guide

Introduction

After weeks of building Django applications, you'll notice the same database queries appearing in your code repeatedly. Knowing which ORM methods to reach for—and when—separates inefficient backends from production-ready systems. Django ORM queries you will use daily form the foundation of scalable data access patterns, whether you're filtering products by price, loading user posts with their comments, or calculating monthly revenue totals.

In this guide, we'll walk through the exact queries your team will write hundreds of times: filtering with lazy evaluation, joining related data efficiently, and aggregating metrics without writing raw SQL. By the end, you'll understand not just how to write these queries, but when to use each one and how to spot performance problems before they hit production.

Filter and order — QuerySets are lazy until evaluated

Every Django ORM interaction starts with a QuerySet. The critical insight is that QuerySets don't execute immediately. They're lazy—meaning you can chain methods indefinitely without hitting the database until you actually need the data.

This is powerful because it lets you build complex queries step-by-step, and it means filtering happens in the database (where it's fast) rather than in Python memory (where it's slow).

Here's a practical scenario: you're building an e-commerce product listing that needs to show items under $50, sorted by newest first, limited to 20 results.

products = Product.objects.filter(price__lt=50).order_by("-created_at")[:20]

Notice nothing executes yet. The query builds up through each method call. Only when you iterate—for product in products—or convert to a list, does Django fire the actual SQL SELECT statement.

Django's field lookups are extensive. Common ones you'll use constantly:

• __lt – less than
• __lte – less than or equal
• __gt – greater than
• __in – matches any value in a list
• __icontains – case-insensitive substring match
• __startswith – string prefix match
• __date – extract date from datetime field
• __isnull – test for NULL in database

Chaining filters with and logic is straightforward:

# All active products under $50, created in the last 30 days
from django.utils import timezone
from datetime import timedelta

cutoff = timezone.now() - timedelta(days=30)
cheap_recent = Product.objects.filter(
    is_active=True,
    price__lt=50,
    created_at__gte=cutoff
).order_by("-created_at")

For or logic, use Q objects:

from django.db.models import Q

# Products under $50 OR marked as featured
products = Product.objects.filter(
    Q(price__lt=50) | Q(is_featured=True)
)

Remember: calling .count() or .exists() on a QuerySet is efficient because Django translates these to SQL COUNT(*) and EXISTS queries, not pulling rows into memory.

select_related — Join foreign keys in one query

When your models have foreign keys, accessing related objects triggers extra database queries. This is the classic N+1 problem.

Imagine you have a Product with a foreign key to Category:

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

class Product(models.Model):
    name = models.CharField(max_length=200)
    category = models.ForeignKey(Category, on_delete=models.CASCADE)
    price = models.DecimalField(max_digits=10, decimal_places=2)

Without optimization, this code runs 21 queries (1 for products + 20 for each category):

# BAD: 1 + N queries
products = Product.objects.all()[:20]
for product in products:
    print(product.category.name)  # Extra query each time

Use select_related to join the foreign key in a single query:

# GOOD: 1 query total
products = Product.objects.select_related("category").all()[:20]
for product in products:
    print(product.category.name)  # No extra query

Under the hood, Django performs a SQL JOIN and builds the related Category object alongside each Product. The syntax is simple: pass the foreign key field name as a string.

For nested relations, use double underscores:

# If Category has a foreign key to Region
products = Product.objects.select_related("category__region").all()

select_related works only for foreign keys and one-to-one relationships because they're single objects. The JOIN is deterministic—one category per product.

prefetch_related — Load many-to-many and reverse relations in a second query

Many-to-many and reverse foreign keys can't use select_related because they return multiple objects. Instead, use prefetch_related, which fetches related objects in a separate query and stitches them together in Python.

Suppose Product has a many-to-many field for tags:

class Tag(models.Model):
    name = models.CharField(max_length=50)

class Product(models.Model):
    name = models.CharField(max_length=200)
    tags = models.ManyToManyField(Tag)

Without optimization, accessing tags in a loop causes N+1:

# BAD: 1 + N queries
products = Product.objects.all()[:20]
for product in products:
    print(product.tags.all())  # Extra query per product

Use prefetch_related:

# GOOD: 2 queries total
products = Product.objects.prefetch_related("tags").all()[:20]
for product in products:
    print(product.tags.all())  # No extra query

This runs 2 queries: one to fetch products, one to fetch all related tags, then combines them in memory. The result is dramatically faster than N+1.

You can combine both methods:

# Optimized query
products = Product.objects.select_related("category").prefetch_related("tags").all()[:20]

For reverse foreign keys (a Category accessing all its Products), use the reverse relation name:

# Fetch categories with all related products
categories = Category.objects.prefetch_related("product_set").all()

Django allows custom prefetch logic with Prefetch objects for advanced cases:

from django.db.models import Prefetch

# Prefetch only active products for each category
prefetch = Prefetch("product_set", queryset=Product.objects.filter(is_active=True))
categories = Category.objects.prefetch_related(prefetch).all()

Aggregate — Push counting and sums into SQL

Calculating metrics like totals, averages, and counts is expensive in Python. Always push aggregation into the database.

Common aggregates are Count, Sum, Avg, Min, and Max:

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

# Total revenue from all orders
total = Order.objects.aggregate(Sum("total_amount"))
# Returns: {"total_amount__sum": 50000}

# Average order value
avg_order = Order.objects.aggregate(Avg("total_amount"))
# Returns: {"total_amount__avg": 250.50}

# How many products are in stock
in_stock_count = Product.objects.filter(stock__gt=0).aggregate(Count("id"))
# Returns: {"id__count": 1542}

You can combine multiple aggregates in one query:

# Get multiple stats in one database hit
stats = Order.objects.aggregate(
    total_orders=Count("id"),
    total_revenue=Sum("total_amount"),
    avg_order_value=Avg("total_amount"),
    min_order=Min("total_amount"),
    max_order=Max("total_amount")
)
# Returns a dict with all five stats

For group-by aggregation, use annotate. This is how you calculate metrics per category, per user, or per time period:

from django.db.models import Count

# Revenue by category
category_stats = Product.objects.values("category").annotate(
    product_count=Count("id"),
    total_products=Count("id")
).order_by("category")

# Result might be:
# [
#   {"category": 1, "product_count": 42},
#   {"category": 2, "product_count": 18},
# ]

A real-world example: calculating monthly revenue from orders:

from django.db.models import Sum
from django.db.models.functions import TruncMonth

# Revenue by month
monthly_revenue = Order.objects.annotate(
    month=TruncMonth("created_at")
).values("month").annotate(
    revenue=Sum("total_amount")
).order_by("month")

Annotate is powerful because it adds fields to each result row without fetching the entire dataset into memory. The filtering and grouping happen in SQL.

Common mistakes

We've all written these. Watch out for them:

• Forgetting select_related on foreign keys. It's easy to miss, but each missing select_related is another database hit per row. Code review helps here.
• Using prefetch_related then filtering in Python. If you prefetch_related("tags") then call .filter(tags__name="red"), Django will re-query. Put filters before prefetch when possible.
• Calling .count() inside a loop. for product in products: if product.reviews.count() > 5: fires a query per product. Annotate instead: .annotate(review_count=Count("reviews")).
• Pulling all rows to memory then slicing. Product.objects.all().reverse()[:10] fetches every row. Use .order_by("-id")[:10] instead.
• N+1 in serializers. Django REST framework can trigger N+1 if your serializer accesses related fields without select_related. Optimize at the view level, not the serializer.

Key takeaways

• QuerySets are lazy. Chain filters, order, and slicing before evaluation.
• Use select_related for foreign keys and one-to-one fields to JOIN them in a single query.
• Use prefetch_related for many-to-many and reverse relations to fetch in a second query.
• Combine both: select_related("category").prefetch_related("tags") is common and efficient.
• Push aggregation into the database with Count, Sum, Avg, and friends.
• Use annotate for group-by queries (per category, per user, per month).
• Enable query logging in development to spot N+1 problems early: django.db.backends logger at DEBUG level.
• Profile with Django Debug Toolbar to visualize query counts and execution time.