ORM Deep Dive — QuerySet API

Django ORM generates SQL from Python code. QuerySet is lazily evaluated — the actual SQL runs only when needed.

---

Model Definition

# products/models.py
from django.db import models
from django.contrib.auth import get_user_model

User = get_user_model()


class Category(models.Model):
    name = models.CharField(max_length=100, unique=True)
    slug = models.SlugField(unique=True)

    class Meta:
        verbose_name_plural = "categories"
        ordering = ["name"]

    def __str__(self):
        return self.name


class Product(models.Model):
    name = models.CharField(max_length=200)
    description = models.TextField(blank=True)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    stock = models.PositiveIntegerField(default=0)
    category = models.ForeignKey(
        Category,
        on_delete=models.SET_NULL,
        null=True,
        related_name="products",
    )
    owner = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="products",
    )
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    is_active = models.BooleanField(default=True)

    class Meta:
        ordering = ["-created_at"]
        indexes = [
            models.Index(fields=["category", "is_active"]),
        ]

---

Basic CRUD

from products.models import Product, Category

# CREATE
product = Product.objects.create(
    name="Python Book",
    price=35000,
    stock=100,
    category=Category.objects.get(slug="books"),
    owner_id=1,
)

# READ — lazy evaluation
all_products = Product.objects.all()
active = Product.objects.filter(is_active=True)

# Single object
try:
    product = Product.objects.get(pk=1)   # raises DoesNotExist if missing
except Product.DoesNotExist:
    product = None

product = Product.objects.filter(pk=1).first()  # None if missing

# UPDATE
Product.objects.filter(pk=1).update(stock=50)  # SQL UPDATE
product.name = "Python Mastery"
product.save()

# DELETE
Product.objects.filter(is_active=False).delete()
product.delete()

---

Filtering — Lookup Operators

from django.db.models import Q

# Comparisons
Product.objects.filter(price__gt=10000)
Product.objects.filter(price__range=(10000, 50000))

# String lookups
Product.objects.filter(name__icontains="python")   # LIKE '%python%' (case-insensitive)
Product.objects.filter(name__startswith="Python")
Product.objects.filter(name__regex=r"^Py.*$")

# NULL checks
Product.objects.filter(category__isnull=True)
Product.objects.exclude(category__isnull=True)

# Related field filter (JOIN)
Product.objects.filter(category__name="Books")
Product.objects.filter(owner__email__endswith="@example.com")

# AND
Product.objects.filter(is_active=True, stock__gt=0)

# OR with Q objects
Product.objects.filter(
    Q(price__lt=10000) | Q(name__icontains="free")
)

# NOT
Product.objects.filter(~Q(is_active=False))

# Ordering
Product.objects.order_by("price")
Product.objects.order_by("-price")
Product.objects.order_by("category__name", "-created_at")

---

select_related / prefetch_related — Solving N+1

# ❌ N+1 problem: 100 products → 101 queries
products = Product.objects.all()
for product in products:
    print(product.category.name)  # extra query per loop

# ✅ select_related: ForeignKey/OneToOne — single JOIN
products = Product.objects.select_related("category", "owner").all()
for product in products:
    print(product.category.name)  # no extra queries

# ✅ prefetch_related: ManyToMany/reverse FK — separate query + Python merge
products = Product.objects.prefetch_related("reviews").all()
for product in products:
    print(product.reviews.count())  # no extra queries

# Combined optimization
from django.db.models import Prefetch

products = Product.objects.select_related(
    "category", "owner"
).prefetch_related(
    Prefetch(
        "reviews",
        queryset=Review.objects.select_related("author").order_by("-created_at"),
    )
).filter(is_active=True)

---

Aggregation and Annotation

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

# aggregate: overall aggregation
stats = Product.objects.filter(is_active=True).aggregate(
    total=Count("id"),
    avg_price=Avg("price"),
    max_price=Max("price"),
    total_stock=Sum("stock"),
)

# annotate: per-row aggregation
categories = Category.objects.annotate(
    product_count=Count("products"),
    avg_price=Avg("products__price"),
).filter(product_count__gt=0).order_by("-product_count")


# F expressions: compare/operate on fields
low_stock = Product.objects.filter(stock__lt=F("reorder_point"))

# 10% price increase
Product.objects.filter(category__slug="books").update(
    price=F("price") * 1.1
)

---

Custom Manager

from django.db import models


class ActiveManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().filter(is_active=True)

    def in_stock(self):
        return self.get_queryset().filter(stock__gt=0)


class Product(models.Model):
    objects = models.Manager()  # default manager
    active = ActiveManager()    # custom manager


# Usage
Product.active.all()           # is_active=True only
Product.active.in_stock()      # is_active=True AND stock > 0

---

Summary

Feature	Method
Single lookup	.get(pk=1) / .filter().first()
OR condition	Q(a=1) | Q(b=2)
ForeignKey JOIN	select_related("category")
Reverse FK batch	prefetch_related("reviews")
Overall aggregate	.aggregate(total=Count("id"))
Per-row aggregate	.annotate(cnt=Count("reviews"))
Field arithmetic	F("price") * 1.1
Custom query	Override Manager.get_queryset()

select_related and prefetch_related alone solve 80% of Django performance issues.