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
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()
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'))
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
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.
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]
)
)
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
)
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
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
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
⚗️ Virtualenv
Creating and activating the virtual environment:
$ python3 -m venv .venv
$ source .venv/bin/activate
🧩 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
⬆️ Migrate
Migrating the djangoqueries
database to create all required tables:
$ python -m manage migrate
🔬 Tests
Running the defined tests:
$ python -m manage test
📊 Data
Populating the djangoqueries
database with demo data for the blog app:
$ python -m manage loaddata blog/fixtures/blog.json
⚖️ 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/
Top comments (0)