Advanced querying with Django

One of the most powerful features that come with Django is the ORM (object-relational mapping). It allows you to easily fetch data from the database by writing intuitive Python code. However, if you don’t know (maybe read a different article if you do), there is more to Django’s ORM than the simple objects.get and objects.filter. This article aims to take your ability to use the Django’s ORM to the next level.

The example models

For context, all of the queries below reference these models:

class Tag(models.Model):
    name = models.CharField(max_length=50, unique=True)

class Post(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(User, on_delete=models.CASCADE, related_name="posts")
    status = models.CharField(
        max_length=20,
        choices=[("draft", "Draft"), ("published", "Published")],
        default="draft",
    )
    likes = models.PositiveIntegerField(default=0)
    views = models.PositiveIntegerField(default=0)
    published_date = models.DateTimeField(null=True, blank=True)
    tags = models.ManyToManyField(Tag, related_name="posts", blank=True)

class Comment(models.Model):
    post = models.ForeignKey(Post, on_delete=models.CASCADE, related_name="comments")
    content = models.TextField()
    created_at = models.DateTimeField(auto_now_add=True)
    author = models.ForeignKey(User, on_delete=models.CASCADE, related_name="comments")

Use Q objects for complex conditions

Q objects are perfect for queries that involve logical operators. For example, if you want to fetch posts that are either published or written by a certain author:

from django.db.models import Q

posts = Post.objects.filter(Q(status="published") | Q(author__username="johndoe"))

How about all posts that are published after Feb 24, 2025, written by ‘johndoe’, have more than 100 likes and views?

posts = Post.objects.filter(
    Q(published_date__gt="2025-02-24") &
    Q(author__username="johndoe") &
    Q(likes__gt=100) &
    Q(views__gt=100)
)

We can even do negation. Let’s get posts that aren’t drafts and not written by ‘johndoe’:

posts = Post.objects.filter(~Q(status="draft") & ~Q(author__username="johndoe"))

A very good use case for Q objects is building dynamic filtering search forms.

Optimizing with select_related and prefetch_related

Database queries can get slow, especially when dealing with relationships. Django’s select_related and prefetch_related methods help reduce the number of queries and boost performance.

select_related

Use this for foreign key and one-to-one relationships. It performs a SQL JOIN to fetch related objects in a single query. Imagine printing the author’s name for all posts, a standard query might look as follows:

# Hits the database
posts = Post.objects.all()

for post in posts:
    # Hits the database for each iteration
    print(post.author.username)

With select_related:

# Hits the database
# select_related caches the 'author' table
posts = Post.objects.select_related('author').all()

for post in posts
    # Doesn't hit the database
    print(post.author.username)

Note: Since select_related caches the foreign key (or one-to-one) table, avoid using the method for tables with many columns or columns with large-sized values as it might use an excessive amount of memory.

prefetch_related

Use this for many-to-many or reverse foreign key relationships. It fetches related objects in separate queries and caches them. For example, if we want to get all tags for each post, a standard query might be:

# Hits the database
posts = Post.objects.all()

for post in posts:
    # Hits the database for each iteration
    print(post.tags.all())

With prefetch_related:

# Hits the database
posts = Post.objects.prefetch_related('tags').all()

for post in posts:
    # Doesn't the database for each iteration
    print(post.tags.all())

Note: Similarly to select_related, avoid using prefetch_related for tables with many columns or columns with large-sized values.

Aggregations and Annotations

Need to summarize data? Django’s aggregation and annotation tools let you compute values like counts, sums, or averages right in your queries.

• aggregate: Returns a single result for the entire queryset.

from django.db.models import Count, Avg
stats = Post.objects.aggregate(total_posts=Count("id"), avg_likes=Avg("likes"))
print(stats)  # {'total_posts': 42, 'avg_likes': 15.3}

• annotate: Adds a computed field to each object in the queryset.

# Add a comment count to each post
posts = Post.objects.annotate(comment_count=Count("comments"))
for post in posts:
    print(f"{post.title}: {post.comment_count} comments")

These are game-changers for reports or dashboards where you need calculated data without messy loops.

Execute raw SQL

Django’s ORM is fantastic, but sometimes you need the precision of raw SQL. The raw() method lets you run custom SQL while still mapping results to model instances:

# Print all post titles that have more than 100 likes
posts = Post.objects.raw("SELECT * FROM myapp_post WHERE likes > 100")
for post in posts:
    print(post.title)

For even more control, use cursor from django.db.connection to execute arbitrary SQL, but this requires a more complex setup as described here.

Warning: Raw SQL can tie your code to a specific database backend.

Filtering with F Expressions

F expressions let you reference model fields dynamically in queries—great for comparisons or updates involving the same row.

For example, increase the views count for all posts by one:

from django.db.models import F
Post.objects.update(views=F("views") + 1)

Or filter posts where the number of likes exceeds the number of views:

posts = Post.objects.filter(likes__gt=F("views"))

This keeps your logic database-side, avoiding the need to fetch and manipulate data in Python.

Chaining and Reusing Querysets

Querysets in Django are lazy—they don’t hit the database until you evaluate them. This makes chaining filters a powerful technique:

# Get all published posts
base_qs = Post.objects.filter(status="published")
# Get all published posts that have more than 50 likes
popular_qs = base_qs.filter(likes__gt=50)
# Get all published posts that are published after Feb 24, 2025
recent_qs = base_qs.filter(published_date__gte="2025-02-24")

You can reuse base_qs without duplicating effort, and Django optimizes the final query when it’s executed. This can be used interchangeably with Q objects.

Conclusion

Let’s summaize all techniques that we’ve gone through:

1. Q objects: Used for queries that involve logical operations
2. select_related and prefetch_related: Used for referencing external relationships
3. Aggregations and annotations: Used for computing data for model instances
4. Raw SQL: Self-explanatory. Used for raw SQL queries
5. F expressions: Used for comparisons and updates on the same row
6. Chaining and reusing querysets: Used for applying multiple queries on top of each other

References

1. Complex lookups with Q objects
2. Django’s select_related
3. Django’s prefetch_related
4. Django’s aggregation
5. Django’s annotate
6. Performing raw SQL queries
7. F() expressions
8. Chaining filters