DEV Community

Cover image for Updating a Django queryset with annotation and subquery
Paolo Melchiorre
Paolo Melchiorre

Posted on โ€ข Originally published at paulox.net on

11 3

Updating a Django queryset with annotation and subquery

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

Preface

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.

Models

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

Python

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
Enter fullscreen mode Exit fullscreen mode

Issue

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

Python

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
    blog.save()
Enter fullscreen mode Exit fullscreen mode

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:

Python

Blog.objects.update(rating=Avg('entry__rating'))
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Solution

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

Python

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

Blog.objects.update( 
    rating=Subquery( 
        Blog.objects.filter( 
            id=OuterRef('id') 
        ).annotate( 
            avg_rating=Avg('entry__rating') 
        ).values('avg_rating')[:1] 
    ) 
)
Enter fullscreen mode Exit fullscreen mode

On PostgreSQL, the SQL looks like:

SQL

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
)
Enter fullscreen mode Exit fullscreen mode

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.

License

This article is released with Creative Commons Attribution ShareAlike license.

creativecommons.org/licenses/by-sa

Source Code

I published the source code used in this article on GitHub.

github.com/pauloxnet/django_queries

GitHub logo pauloxnet / djangoqueries

The code of "Making queries" in docs.djangoproject.com that I used in my article "Full-Text Search in Django with PostgreSQL".

๐Ÿฆ„๏ธ Django Queries

Code style: ruff Coverage Mastodon Follow

Source code used in my article "Full-Text Search in Django with PostgreSQL" based on the blog application defined in the Django documentation topic "Making queries".

๐Ÿ“– Documentation

๐Ÿ—ƒ๏ธ Database

Creating the djangoqueries database in your local PostgreSQL instance:

$ createdb -U postgres -O postgres djangoqueries
Enter fullscreen mode Exit fullscreen mode

โš—๏ธ Virtualenv

Creating and activating the virtual environment:

$ python3 -m venv .venv
$ source .venv/bin/activate
Enter fullscreen mode Exit fullscreen mode

๐Ÿงฉ Requirements

Installing the latest version of django (tested from version 1.11 to 5.1) and psycopg (tested from version 2.7 to 3.2) using the requirements file:

$ python -m pip install -r requirements.txt
Enter fullscreen mode Exit fullscreen mode

โฌ†๏ธ Migrate

Migrating the djangoqueries database to create all required tables:

$ python -m manage migrate
Enter fullscreen mode Exit fullscreen mode

๐Ÿ”ฌ Tests

Running the defined tests:

$ python -m manage test
Enter fullscreen mode Exit fullscreen mode

๐Ÿ“Š Data

Populating the djangoqueries database with demo data for the blog app:

$ python -m manage loaddata blog/fixtures/blog.json
Enter fullscreen mode Exit fullscreen mode

โš–๏ธ License

Django Queries is licensedโ€ฆ

Share

Original

Originally posted on my blog:

paulox.net/2018/10/01/updating-a-django-queryset-with-annotation-and-subquery/

API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more โ†’

Top comments (0)

AWS Security LIVE!

Join us for AWS Security LIVE!

Discover the future of cloud security. Tune in live for trends, tips, and solutions from AWS and AWS Partners.

Learn More

๐Ÿ‘‹ Kindness is contagious

Please leave a โค๏ธ or a friendly comment on this post if you found it helpful!

Okay