How to Use Subquery in Django ORM: Syntax and Examples

Use Subquery in Django ORM to embed one queryset inside another for filtering or annotating results. It allows you to perform nested queries by passing a queryset to Subquery() and using it in filters or annotations.

Syntax

The Subquery expression takes a queryset as input and can be used inside filter(), annotate(), or values() to nest queries. You typically use it with OuterRef to refer to fields from the outer queryset.

• Subquery(queryset): Wraps the inner queryset.
• OuterRef('field'): Refers to a field from the outer queryset inside the subquery.
• Use inside filter() or annotate() to compare or add values.

python

from django.db.models import OuterRef, Subquery

# Basic syntax pattern
inner_qs = ModelB.objects.filter(foreign_key=OuterRef('pk')).values('some_field')[:1]

outer_qs = ModelA.objects.annotate(
    sub_value=Subquery(inner_qs)
)

# Now outer_qs has a new field 'sub_value' from the subquery

Example

This example shows how to annotate each Author with the title of their latest Book using Subquery. It demonstrates nested querying to get related data efficiently.

python

from django.db import models
from django.db.models import OuterRef, Subquery

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

class Book(models.Model):
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    title = models.CharField(max_length=100)
    published_date = models.DateField()

# Subquery to get latest book title per author
latest_book_qs = Book.objects.filter(
    author=OuterRef('pk')
).order_by('-published_date').values('title')[:1]

# Annotate authors with their latest book title
authors_with_latest_book = Author.objects.annotate(
    latest_book_title=Subquery(latest_book_qs)
)

for author in authors_with_latest_book:
    print(f"{author.name}: {author.latest_book_title}")

Output

Alice: Wonderland Adventures
Bob: Space Odyssey
Carol: Deep Sea Tales

Common Pitfalls

• Not slicing the inner queryset with [:1] causes errors because Subquery expects a single value.
• Forgetting to use OuterRef inside the subquery will make it unable to reference the outer queryset fields.
• Using Subquery without proper ordering can lead to unpredictable results.

python

from django.db.models import OuterRef, Subquery

# Wrong: no slicing, will raise error
wrong_qs = Book.objects.filter(author=OuterRef('pk')).values('title')

# Right: slice to one result
right_qs = Book.objects.filter(author=OuterRef('pk')).values('title')[:1]

# Wrong: no OuterRef, cannot link to outer queryset
wrong_subquery = Book.objects.filter(author=1).values('title')[:1]

# Right: use OuterRef to link dynamically
right_subquery = Book.objects.filter(author=OuterRef('pk')).values('title')[:1]

Quick Reference

Remember these key points when using Subquery in Django ORM:

• Always slice the inner queryset to one result with [:1].
• Use OuterRef to refer to outer queryset fields inside the subquery.
• Use Subquery inside annotate() or filter() for nested queries.
• Order the inner queryset to control which record is returned.

Key Takeaways

Use Subquery with OuterRef to nest one queryset inside another in Django ORM.

Always slice the inner queryset with [:1] to return a single value for Subquery.

Subquery can be used in annotate() or filter() to add or filter by related data.

Ordering the inner queryset controls which record Subquery returns.

Common mistakes include missing slicing or forgetting OuterRef in the subquery.