Blogs

Django ORM Cheatsheet

Posted by JCharis AI

June 25, 2025, 8:36 p.m.

Django ORM Cheatsheet

Advanced Django ORM Cheatsheet

This cheatsheet covers advanced Django ORM techniques, performance tips, and some modern features relevant in 2025.

Query Optimization & Performance

• Select Related:
  Fetch related objects in a single query (for ForeignKeys).

  books = Book.objects.select_related('author').all()
  

• Prefetch Related:
  For ManyToMany or reverse ForeignKey lookups.

  books = Book.objects.prefetch_related('genres', 'reviews').all()
  

• Only and Defer:
  Retrieve only specific fields (for large models).

  authors = Author.objects.only('name', 'birth_date')
  books = Book.objects.defer('large_field')
  

• Using Database Functions:
  Utilize built-in DB functions.

  from django.db.models import Count, F, Q, Value
  from django.db.models.functions import Coalesce
  authors = Author.objects.annotate(num_books=Count('book'))
  

Advanced Filtering & Lookups

• Complex Queries with Q:

  from django.db.models import Q
  Book.objects.filter(Q(title__icontains='django') | Q(genre='FIC'))
  

• Exclude:

  books = Book.objects.exclude(genre='FIC')
  

• F Expressions (Reference Other Fields):

  Book.objects.filter(pages__gt=F('copies_sold'))
  Book.objects.update(price=F('price') * 1.1)
  

• Subqueries & OuterRef:

  from django.db.models import OuterRef, Subquery
  recent_review = Review.objects.filter(book=OuterRef('pk')).order_by('-created_at')
  Book.objects.annotate(
      latest_review=Subquery(recent_review.values('text')[:1])
  )
  

• Database Functions (e.g. Coalesce):

  from django.db.models.functions import Coalesce
  Book.objects.annotate(
      safe_rating=Coalesce('rating', Value(0))
  )
  

Bulk Operations & Raw SQL

• Bulk Create/Update:

  Book.objects.bulk_create([...])
  Book.objects.bulk_update([...], ['price'])
  

• Raw SQL:

  Book.objects.raw('SELECT * FROM books_book WHERE pages > %s', [300])
  

• Extra (Discouraged in New Code): For edge cases only, prefer annotate and RawSQL.

Aggregation & Annotation

• Aggregation:

  from django.db.models import Avg, Max, Min, Sum
  Book.objects.aggregate(Avg('pages'), Sum('copies_sold'))
  

• Annotation:

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

Async ORM (2025+)

• Async Queries:

  async for book in Book.objects.filter(pages__gt=300).aiterator():
      print(book.title)
  

  (When using Django’s async views or scripts)[5].

Advanced Model Usage

• Custom Managers:

  class ActiveManager(models.Manager):
      def get_queryset(self):
          return super().get_queryset().filter(is_active=True)
  class Author(models.Model):
      is_active = models.BooleanField(default=True)
      objects = models.Manager()
      active = ActiveManager()
  

• QuerySet Chaining:

  books = Book.objects.filter(pages__gt=100).order_by('-publication_date')[:10]
  

Best Practices

• Use select_related/prefetch_related to avoid N+1 queries[4].
• Annotate and aggregate for efficient summary queries.
• Leverage async and database functions for large datasets[5].
• Use only and defer to limit expensive field loads.
• For power users: engage raw SQL or Subquery expressions for complex use cases, but validate all user input to avoid SQL injection.

Django's ORM supports highly expressive and powerful annotations using SQL-like CASE statements directly in Python. This is useful for creating computed fields, conditional aggregations, and even complex reporting, all at the QuerySet level.

Core Tools: Case, When, and F

• Case: Acts like SQL's CASE, allowing conditional logic.
• When: Represents individual branches/conditions within a Case.
• F: References model fields directly.

Example Models Context

Assume models:

class Post(models.Model):
    title = models.CharField(max_length=200)
    status = models.CharField(max_length=20, choices=[("draft", "Draft"), ("published", "Published")], default="draft")
    likes = models.PositiveIntegerField(default=0)
    views = models.PositiveIntegerField(default=0)
    # ...other fields

Example: Conditional Annotation

Suppose you want to annotate posts with a "popularity" label based on their number of likes:

from django.db.models import Case, When, Value, CharField

Post.objects.annotate(
    popularity=Case(
        When(likes__gte=1000, then=Value('Hot')),
        When(likes__gte=100, then=Value('Trending')),
        default=Value('Regular'),
        output_field=CharField(),
    )
)

• What this does:
  Adds a .popularity attribute to each Post instance in the queryset, based on likes thresholds[3].

Example: Conditional Aggregation

To count only published posts per user:

from django.db.models import Count, Q

User.objects.annotate(
    published_count=Count('posts', filter=Q(posts__status='published'))
)

• What this does:
  For each User, adds published_count for related posts with status 'published'[3][4].

Example: Using F Expressions

Increase likes conditionally:

from django.db.models import F

Post.objects.filter(likes__lt=10).update(likes=F('likes') + 1)

Combining Complex Logic

You can nest Case, When, and aggregation for advanced reporting:

from django.db.models import IntegerField

Post.objects.annotate(
    engagement_level=Case(
        When(likes__gte=500, views__gte=10000, then=Value(3)),
        When(likes__gte=100, views__gte=2000, then=Value(2)),
        default=Value(1),
        output_field=IntegerField()
    )
)

Best Practices

• Use annotations and conditional expressions to push computation to the database—this often results in more efficient queries and less Python-side work.
• These techniques are highly useful for dashboards, reporting, or whenever you need dynamic, calculated fields at query time.

Further Learning:
Check out Django's documentation on conditional expressions and resources on advanced querying for deeper examples and performance tips.

Summary Table: Common Syntax

These advanced annotation features enable SQL-level power in Pythonic code, letting you build expressive, efficient queries with Django ORM.