Updating a Django queryset with annotation and subquery

How-To guide to update a Django queryset with annotation and subquery


© 2018 Paolo Melchiorre "First day of autumn at the Cerrano tower" instagram.com/p/BoFRx63CrMS


In the official Django documentation there is no info about using Django ORM update() and annotate() functions to update all rows in a queryset by using an annotated value.

We are going to show a way to update an annotated Django queryset using only Django ORM subquery() without using extra() functions or SQL code.


First, we use the weblog application code, found in the Django Documentation under “Making Queries”.


from django.db import models

class Blog(models.Model):
    name = models.CharField(max_length=100)
    rating = models.DecimalField(max_digits=3, decimal_places=2, default=5)

    def __str__(self):
        return self.name

class Entry(models.Model):
    blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
    headline = models.CharField(max_length=255)
    rating = models.IntegerField(default=5)

    def __str__(self):
        return self.headline


One way to update the Blog’s rating based on the average rating from all the entries could be:


from django.db.models import Avg
from blog.models import Blog

for blog in Blog.objects.annotate(avg_rating=Avg('entry__rating')):
    blog.rating = blog.avg_rating or 0

The code above may be very inefficient and slow if we have a lot of Entries or Blogs because Django ORM performs a SQL query for each step of the for-cycle.

If we want to avoid the code above and perform an update operation in a single SQL-request, we can try and use a code like this:



But this doesn’t work and we will read an error similar to this:

Traceback (most recent call last):
FieldError: Joined field references are not permitted in this query


With Django 1.11+ it is possible to use Django ORM but using subquery():

Subquery() expressions

You can add an explicit subquery to a QuerySet using the Subquery expression.

see documentation


from django.db.models import Avg, OuterRef, Subquery
from blog.models import Blog, Entry


On PostgreSQL, the SQL looks like:


UPDATE "blog_blog"
SET "rating" = (
   SELECT AVG(U1."rating") AS "avg_rating"
   FROM "blog_blog" U0
   LEFT OUTER JOIN "blog_entry" U1 ON (U0."id" = U1."blog_id")
   WHERE U0."id" = ("blog_blog"."id")
   GROUP BY U0."id"
   LIMIT 1

Stack Overflow

I wrote this solution the first time as an answer on Stack Overflow.

If you found this article useful, you can vote for my answer on Stack Overflow and read my other answers on my profile.

 2018-10-01   2 min read   Article   #django #databases #sql #python #article