Skip to content

Django ORM Queries: The 80-20 Guide#

Complete guide to Django ORM covering the 20% of queries you'll use 80% of the time.

🎯 Basic Queries#

Get All Objects#

from myapp.models import Post

# Get all posts
posts = Post.objects.all()

# Iterate
for post in posts:
    print(post.title)

Get Single Object#

# Get by primary key
post = Post.objects.get(pk=1)
post = Post.objects.get(id=1)

# Get by field
post = Post.objects.get(title='My Post')

# Raises DoesNotExist if not found
# Raises MultipleObjectsReturned if multiple found

# Safe get (returns None)
post = Post.objects.filter(pk=1).first()

Filter (WHERE)#

# Basic filter
published_posts = Post.objects.filter(is_published=True)

# Multiple conditions (AND)
posts = Post.objects.filter(is_published=True, author_id=1)

# Chaining filters (AND)
posts = Post.objects.filter(is_published=True).filter(author_id=1)

# Exclude
draft_posts = Post.objects.exclude(is_published=True)

# Complex exclude
posts = Post.objects.exclude(is_published=True, author_id=1)

🔍 Field Lookups#

Exact Match#

# Exact (default)
posts = Post.objects.filter(title='My Post')
posts = Post.objects.filter(title__exact='My Post')

# Case-insensitive
posts = Post.objects.filter(title__iexact='my post')

Contains#

# Contains (case-sensitive)
posts = Post.objects.filter(title__contains='Django')

# Case-insensitive contains
posts = Post.objects.filter(title__icontains='django')

# Starts with
posts = Post.objects.filter(title__startswith='How')

# Case-insensitive starts with
posts = Post.objects.filter(title__istartswith='how')

# Ends with
posts = Post.objects.filter(title__endswith='Guide')

# Case-insensitive ends with
posts = Post.objects.filter(title__iendswith='guide')

Comparison#

# Greater than
posts = Post.objects.filter(views__gt=100)

# Greater than or equal
posts = Post.objects.filter(views__gte=100)

# Less than
posts = Post.objects.filter(views__lt=100)

# Less than or equal
posts = Post.objects.filter(views__lte=100)

# Range
from datetime import datetime, timedelta
week_ago = datetime.now() - timedelta(days=7)
posts = Post.objects.filter(created_at__gte=week_ago)

Date/Time Lookups#

from django.utils import timezone
from datetime import date, timedelta

# Year
posts = Post.objects.filter(created_at__year=2024)

# Month
posts = Post.objects.filter(created_at__month=1)

# Day
posts = Post.objects.filter(created_at__day=15)

# Week day (1=Sunday, 7=Saturday)
posts = Post.objects.filter(created_at__week_day=1)

# Date range
today = date.today()
posts = Post.objects.filter(created_at__date=today)

# Today
posts = Post.objects.filter(created_at__date=timezone.now().date())

# This week
week_start = timezone.now() - timedelta(days=7)
posts = Post.objects.filter(created_at__gte=week_start)

Null/Empty#

# Is null
posts = Post.objects.filter(published_at__isnull=True)

# Is not null
posts = Post.objects.filter(published_at__isnull=False)

# Empty string
posts = Post.objects.filter(excerpt='')

# Not empty
posts = Post.objects.exclude(excerpt='')

In#

# In list
posts = Post.objects.filter(id__in=[1, 2, 3, 5, 8])

# In queryset
author_ids = User.objects.filter(is_staff=True).values_list('id', flat=True)
posts = Post.objects.filter(author_id__in=author_ids)

Regex#

# Case-sensitive regex
posts = Post.objects.filter(title__regex=r'^[A-Z]')

# Case-insensitive regex
posts = Post.objects.filter(title__iregex=r'^[a-z]')

ForeignKey Lookups#

# Get posts by author
posts = Post.objects.filter(author__username='admin')

# Get posts by author's email
posts = Post.objects.filter(author__email='admin@example.com')

# Reverse lookup (from User to Post)
from django.contrib.auth.models import User
user = User.objects.get(username='admin')
posts = user.post_set.all()  # Or user.posts.all() if related_name='posts'

# Multiple levels
posts = Post.objects.filter(author__profile__bio__contains='developer')

ManyToMany Lookups#

# Posts with specific tag
posts = Post.objects.filter(tags__name='Django')

# Posts with any of these tags
posts = Post.objects.filter(tags__name__in=['Django', 'Python'])

# Posts with all these tags
from django.db.models import Q
posts = Post.objects.filter(
    Q(tags__name='Django') & Q(tags__name='Python')
).distinct()

# Posts without tag
posts = Post.objects.exclude(tags__name='Django')

🔀 Q Objects (Complex Queries)#

OR Conditions#

from django.db.models import Q

# OR
posts = Post.objects.filter(
    Q(title__contains='Django') | Q(title__contains='Python')
)

# AND
posts = Post.objects.filter(
    Q(is_published=True) & Q(views__gt=100)
)

# NOT
posts = Post.objects.filter(
    ~Q(is_published=True)
)

# Complex
posts = Post.objects.filter(
    Q(is_published=True) & 
    (Q(title__contains='Django') | Q(content__contains='Django'))
)

Combining Q with filter#

# Q objects can be combined with regular filters
posts = Post.objects.filter(
    is_published=True
).filter(
    Q(title__contains='Django') | Q(content__contains='Django')
)

📊 Sorting#

Order By#

# Ascending
posts = Post.objects.all().order_by('created_at')

# Descending
posts = Post.objects.all().order_by('-created_at')

# Multiple fields
posts = Post.objects.all().order_by('is_published', '-created_at')

# Random
posts = Post.objects.all().order_by('?')

# Reverse existing order
posts = Post.objects.all().order_by('created_at').reverse()

Model Meta Ordering#

# In model
class Post(models.Model):
    class Meta:
        ordering = ['-created_at']  # Default ordering

# Override in query
posts = Post.objects.all().order_by('title')  # Overrides model ordering

🔢 Limiting & Slicing#

Limit Results#

# First 10
posts = Post.objects.all()[:10]

# Skip first 5, get next 10
posts = Post.objects.all()[5:15]

# Last 10 (inefficient, use order_by)
posts = Post.objects.all().order_by('-id')[:10]

# Get single object
post = Post.objects.all()[0]  # First
post = Post.objects.first()   # Better
post = Post.objects.last()    # Last (requires ordering)

Exists & Count#

# Check if exists
if Post.objects.filter(title='My Post').exists():
    print('Post exists')

# Count
count = Post.objects.filter(is_published=True).count()

# Check count
if Post.objects.filter(is_published=True).count() > 10:
    print('More than 10 published posts')

🎯 Select Specific Fields#

Only (Defer Other Fields)#

# Only load specific fields
posts = Post.objects.only('id', 'title', 'created_at')
# Other fields accessed will trigger additional query

# Usage
for post in posts:
    print(post.title)  # No extra query
    print(post.content)  # Extra query to fetch content

Defer (Exclude Fields)#

# Exclude heavy fields
posts = Post.objects.defer('content', 'excerpt')
# Content and excerpt not loaded initially

# Usage
for post in posts:
    print(post.title)  # No extra query
    print(post.content)  # Extra query to fetch content

Values (Dictionary)#

# Get dictionaries instead of model instances
posts = Post.objects.values('id', 'title', 'created_at')

# Returns: [{'id': 1, 'title': 'Post 1', 'created_at': ...}, ...]

# Values with related fields
posts = Post.objects.values('id', 'title', 'author__username')

# Flat values (single field)
titles = Post.objects.values_list('title', flat=True)
# Returns: ['Post 1', 'Post 2', ...]

# Named tuples
posts = Post.objects.values_list('id', 'title', named=True)
# Returns: [Row(id=1, title='Post 1'), ...]

# Reduces queries for ForeignKey
posts = Post.objects.select_related('author', 'category')

# Without select_related (N+1 problem)
for post in Post.objects.all():
    print(post.author.username)  # Query for each post

# With select_related (1 query)
for post in Post.objects.select_related('author').all():
    print(post.author.username)  # No extra query

# Multiple levels
posts = Post.objects.select_related('author__profile')

# Reduces queries for ManyToMany and reverse ForeignKey
posts = Post.objects.prefetch_related('tags', 'comments')

# Without prefetch_related (N+1 problem)
for post in Post.objects.all():
    for tag in post.tags.all():  # Query for each post
        print(tag.name)

# With prefetch_related (2 queries total)
for post in Post.objects.prefetch_related('tags').all():
    for tag in post.tags.all():  # No extra query
        print(tag.name)

# Prefetch with filtering
from django.db.models import Prefetch
posts = Post.objects.prefetch_related(
    Prefetch('tags', queryset=Tag.objects.filter(is_active=True))
)

Combined#

# Use both for optimal performance
posts = Post.objects.select_related('author').prefetch_related('tags')

📈 Aggregate Functions#

Basic Aggregates#

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

# Count
total = Post.objects.count()
published_count = Post.objects.filter(is_published=True).count()

# Using aggregate
from django.db.models import Count
stats = Post.objects.aggregate(
    total=Count('id'),
    published=Count('id', filter=Q(is_published=True))
)

# Sum
total_views = Post.objects.aggregate(total_views=Sum('views'))

# Average
avg_views = Post.objects.aggregate(avg_views=Avg('views'))

# Max/Min
max_views = Post.objects.aggregate(max_views=Max('views'))
min_views = Post.objects.aggregate(min_views=Min('views'))

Group By (annotate)#

from django.db.models import Count, Avg

# Count posts per author
authors = User.objects.annotate(post_count=Count('posts'))

for author in authors:
    print(f"{author.username}: {author.post_count} posts")

# Average views per author
authors = User.objects.annotate(avg_views=Avg('posts__views'))

# Multiple annotations
authors = User.objects.annotate(
    post_count=Count('posts'),
    avg_views=Avg('posts__views'),
    total_views=Sum('posts__views')
)

🏷️ Annotate (Add Computed Fields)#

from django.db.models import Count, F, Value, Case, When

# Add computed field
posts = Post.objects.annotate(
    title_length=Length('title')
)

# Use F() to reference other fields
posts = Post.objects.annotate(
    views_per_day=F('views') / F('days_since_created')
)

# Conditional annotation
posts = Post.objects.annotate(
    status_label=Case(
        When(is_published=True, then=Value('Published')),
        default=Value('Draft'),
        output_field=CharField()
    )
)

# Count related objects
posts = Post.objects.annotate(
    comment_count=Count('comments'),
    tag_count=Count('tags')
)

🔍 Distinct#

# Remove duplicates
posts = Post.objects.filter(tags__name='Django').distinct()

# Important for ManyToMany and reverse ForeignKey
# Without distinct, you get duplicates

🎯 Common Query Patterns (80-20)#

Recent Published Posts#

from django.utils import timezone
from datetime import timedelta

week_ago = timezone.now() - timedelta(days=7)
posts = Post.objects.filter(
    is_published=True,
    created_at__gte=week_ago
).order_by('-created_at')[:10]

from django.db.models import Q

def search_posts(query):
    return Post.objects.filter(
        Q(title__icontains=query) |
        Q(content__icontains=query) |
        Q(excerpt__icontains=query)
    ).distinct()

posts = Post.objects.filter(
    is_published=True
).order_by('-views', '-created_at')[:10]

Posts by Category with Tags#

posts = Post.objects.filter(
    category__slug='django',
    tags__name__in=['tutorial', 'guide']
).select_related('category', 'author').prefetch_related('tags').distinct()

⚡ Performance Tips#

  1. ✅ Use select_related for ForeignKey/OneToOne
  2. ✅ Use prefetch_related for ManyToMany/reverse ForeignKey
  3. ✅ Use only()/defer() for large text fields
  4. ✅ Use values()/values_list() when you don't need model instances
  5. ✅ Add .distinct() after ManyToMany filters
  6. ✅ Use .exists() instead of .count() > 0
  7. ✅ Use .first() instead of [0]
  8. ✅ Index frequently queried fields

✅ Next Steps#

Pro Tip: Use django-debug-toolbar to see all SQL queries. Most performance issues come from N+1 queries - use select_related and prefetch_related!