Updating a Django queryset with annotation and subquery
Tags: django, database, sql, python
Published: 2018-10-02 07:30:46

---
title: Updating a Django queryset with annotation and subquery
published: true
description: How-To guide to update a Django queryset with annotation and subquery
tags: django,databases,sql,python
canonical_url: https://www.paulox.net/2018/10/01/updating-a-django-queryset-with-annotation-and-subquery/
cover_image: https://thepracticaldev.s3.amazonaws.com/i/w59l6fh4yz2xew0f61yr.jpg
series: PauLoX's Article
---

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

## Preface

In the official Django [documentation](https://docs.djangoproject.com) 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"](https://docs.djangoproject.com/en/2.0/topics/db/queries/).

`Python`
```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`
```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`
```python
Blog.objects.update(rating=Avg('entry__rating'))
```

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

```shell
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.
>
> *[see documentation](https://docs.djangoproject.com/en/2.1/ref/models/expressions/#subquery-expressions)*

`Python`
```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`
```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](https://stackoverflow.com/a/50134728/755343) on Stack Overflow and read my other answers on [my profile](https://stackoverflow.com/users/755343).

# License

This article is released with Creative Commons Attribution ShareAlike license.  
[creativecommons.org/licenses/by-sa](https://creativecommons.org/licenses/by-sa/4.0/)

# Source Code

I published the source code used in this article on GitHub.  
[github.com/pauloxnet/django_queries](https://github.com/pauloxnet/django_queries/)

{% github pauloxnet/django_queries %}

# Share

{% twitter 1046889297410953216 %}

{% instagram BoFRx63CrMS %}

# Original

Originally posted on my blog:

[paulox.net/2018/10/01/updating-a-django-queryset-with-annotation-and-subquery/](https://www.paulox.net/2018/10/01/updating-a-django-queryset-with-annotation-and-subquery/)