speed up django views with Subquery

description.txt

Code snipptes from the lightning talk at the Django user group Berlin from Oct. 17th 2017.

models.py

from django.db import models


class Blog(models.Model):
    title = models.CharField(max_length=100)

    @property
    def latest_post(self):
        return self.post_set.latest('created')


class Post(models.Model):
    blog = models.ForeignKey(Blog)
    created = models.DateTimeField(auto_now_add=True)
    content = models.TextField()

    @property
    def has_comment(self):
        return self.comment_set.exists()


class Comment(models.Model):
    post = models.ForeignKey(Post)
    content = models.TextField()

views.py

from django.views.generic import ListView
from django.db.models import Exists, Subquery, OuterRef

from .models import Blog, Post, Comment


# Problem: How to list all blogs where the latest post has a comment.

# this is how not to do it
class OldView(ListView):
    model = Blog
    template_name = 'whatever.html'
    paginate_by = 10

    def get_queryset(self):
        qs = Blog.objects.all()

        return [
            blog for blog in qs
            if blog.latest_post.has_comment
        ]


# this is the more performant way 
class NewView(ListView):
    model = Blog
    template_name = 'whatever.html'
    paginate_by = 10

    def get_queryset(self):
        posts_subquery = Post.objects\
            .filter(blog=OuterRef('pk'))\
            .order_by('-created')\
            .values('pk')

        comments_subquery = Comment.objects\
            .filter(post=OuterRef('latest_post_id'))

        return Blog.objects\
            .annotate(latest_post_id=Subquery(posts_subquery[:1]),
                      latest_post_has_comment=Exists(comments_subquery))\
            .filter(latest_post_has_comment=True)

Cool! 👍 It's a simple example which explains a powerful concept.