Django ORM Reference

1. QuerySet Basics

from myapp.models import Article, Author

# Retrieve all
articles = Article.objects.all()

# Filter & exclude
active = Article.objects.filter(is_published=True)
not_draft = Article.objects.exclude(status="draft")

# Chaining (lazy — SQL runs only on evaluation)
recent = (Article.objects
    .filter(is_published=True)
    .exclude(category=None)
    .order_by("-created_at")[:10])

# Get single object
article = Article.objects.get(pk=1)              # raises DoesNotExist if not found
article = Article.objects.filter(pk=1).first()   # returns None if not found

# get_or_create, update_or_create
obj, created = Article.objects.get_or_create(
    slug="hello-world",
    defaults={"title": "Hello World", "author": author},
)

# Bulk operations
Article.objects.bulk_create([Article(title=t) for t in titles])
Article.objects.filter(status="draft").update(status="published")
Article.objects.filter(is_published=False).delete()

2. Field Lookups

# Exact (default)
Article.objects.filter(status="published")
Article.objects.filter(status__exact="published")

# String lookups
Article.objects.filter(title__icontains="python")   # case-insensitive contains
Article.objects.filter(title__startswith="How")
Article.objects.filter(slug__regex=r"^\d{4}-")

# Numeric / date comparisons
Article.objects.filter(view_count__gte=1000)
Article.objects.filter(created_at__year=2024)
Article.objects.filter(created_at__date__gte="2024-01-01")
Article.objects.filter(published_at__range=("2024-01-01", "2024-12-31"))

# Relationship traversal (double underscore)
Article.objects.filter(author__name="Alice")
Article.objects.filter(tags__name__in=["python", "django"])

# Null checks
Article.objects.filter(published_at__isnull=True)
Article.objects.exclude(cover_image="")

3. Q Objects & F Expressions

from django.db.models import Q, F

# Q objects — complex boolean logic
Article.objects.filter(
    Q(status="published") | Q(featured=True)
)
Article.objects.filter(
    Q(author__name="Alice") & ~Q(category="spam")
)

# F expressions — reference field values in queries
# Increment view count without Python roundtrip
Article.objects.filter(pk=1).update(view_count=F("view_count") + 1)

# Compare two fields
Article.objects.filter(updated_at__gt=F("created_at"))

# Annotate with F arithmetic
from django.db.models import ExpressionWrapper, DurationField
Article.objects.annotate(
    age=ExpressionWrapper(
        Now() - F("created_at"),
        output_field=DurationField()
    )
)

4. Aggregation & Annotation

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

# Aggregate entire queryset
stats = Article.objects.aggregate(
    total=Count("id"),
    avg_views=Avg("view_count"),
    max_views=Max("view_count"),
)

# Annotate each row
authors = Author.objects.annotate(
    article_count=Count("articles"),
    total_views=Sum("articles__view_count"),
).order_by("-article_count")

# Conditional aggregation
from django.db.models import Case, When, IntegerField
authors = Author.objects.annotate(
    published_count=Count(
        Case(When(articles__status="published", then=1),
             output_field=IntegerField())
    )
)

# values() + annotate = GROUP BY
Article.objects.values("category").annotate(
    count=Count("id"),
    avg_views=Avg("view_count"),
).order_by("-count")

5. select_related & prefetch_related

from django.db.models import Prefetch

# select_related — JOIN for ForeignKey / OneToOne (single query)
articles = Article.objects.select_related("author", "category").all()
# Access without extra queries:
for a in articles:
    print(a.author.name, a.category.name)

# prefetch_related — separate query for ManyToMany / reverse FK
articles = Article.objects.prefetch_related("tags", "comments").all()

# Custom prefetch with filtering/ordering
articles = Article.objects.prefetch_related(
    Prefetch("comments",
             queryset=Comment.objects.filter(is_approved=True).order_by("-created_at"),
             to_attr="approved_comments")
)

# Defer / only — select specific columns
Article.objects.only("id", "title", "slug")   # fetch only these columns
Article.objects.defer("content", "metadata")  # skip heavy fields

6. Common Lookup Suffixes