Django update queryset with annotation

Question

I want to update all rows in queryset by using annotated value.

I have a simple models:

class Relation(models.Model):
    rating = models.IntegerField(default=0)

class SignRelation(models.Model):
    relation = models.ForeignKey(Relation, related_name='sign_relations')
    rating = models.IntegerField(default=0)

And I want to awoid this code:

for relation in Relation.objects.annotate(total_rating=Sum('sign_relations__rating')):
    relation.rating = relation.total_rating or 0
    relation.save()

And do update in one SQL-request by using something like this:

Relation.objects.update(rating=Sum('sign_relations__rating'))

Doesn't work:

TypeError: int() argument must be a string or a number, not 'Sum'

or

Relation.objects.annotate(total_rating=Sum('sign_relations__rating')).update(rating=F('total_rating'))

Also doesn't work:

DatabaseError: missing FROM-clause entry for table "relations_signrelation"
LINE 1: UPDATE "relations_relation" SET "rating" = SUM("relations_si...

Is it possible to use Django's ORM for this purpose? There is no info about using update() and annotate() together in docs.

Answer 1

For Django 1.11+ you can use Subquery:

from django.db.models import OuterRef, Subquery, Sum

Relation.objects.update(
    rating=Subquery(
        Relation.objects.filter(
            id=OuterRef('id')
        ).annotate(
            total_rating=Sum('sign_relations__rating')
        ).values('total_rating')[:1]
    )
)

This code produce the same SQL code proposed by Tomasz Jakub Rup but with no use of RawSQL expression. The Django documentation warns against the use of RawSQL due to the possibility of SQL injection).

Update

I published an article based on this answer with more in-depth explanations: Updating a Django queryset with annotation and subquery on paulox.net

Answer 2

UPDATE statement doesn't support GROUP BY. See e.g. PostgreSQL Docs, SQLite Docs.

You need someting like this:

UPDATE relation
SET rating = (SELECT SUM(rating)
              FROM sign_relation
              WHERE relation_id = relation.id)

Equivalent in DjangoORM:

from django.db.models.expressions import RawSQL

Relation.objects.all(). \
    update(rating=RawSQL('SELECT SUM(rating) FROM signrelation WHERE relation_id = relation.id', []))

or:

from django.db.models import F, Sum
from django.db.models.expressions import RawSQL

Relation.objects.all(). \
    update(rating=RawSQL(SignRelation.objects. \
                         extra(where=['relation_id = relation.id']). \
                         values('relation'). \
                         annotate(sum_rating=Sum('rating')). \
                         values('sum_rating').query, []))