Allow update() with aggregates and joins via subqueries

[jorgecarleitao]

Given the following 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)

support queries like

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

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

to avoid queries like

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

This is useful to populate models that contain redundant data.

Based on ​this question in SO.

[Josh Smeaton]

Expressions work in update() and now also create() too. What (I think) you're asking for here is to support aggregates in update queries by pushing the aggregate portion into a subquery. Supporting joins in an update query is also related, and would require either subqueries or an UPDATE .. FROM .. syntax that I think only SQL Server supports (so let's forget that!).

I'm not sure what capabilities the ORM has with regard to detecting and then pushing aggregates down into subqueries though, so this is probably going to be a difficult thing to implement. Anssi would probably have a better idea on the capabilities.

Regardless though, it's supported in SQL so we should try to support it too.

[Anssi Kääriäinen]

We don't have particularly good support for generating subqueries for aggregation. We need that for other purposes, too.

I'm +1 for doing this, but there is likely a lot to do here.

[Marc Tamlyn]

Agreed this would be great. For what it's work, update .. from .. definitely works in PG as well.

[Mads Jensen]

1.11 includes Subquery (and OuterRef that could probably be of use in some way) make this something less cumbersome to implement support for.

[Shai Berger]

Replying to Marc Tamlyn:

For what it's worth, update .. from .. definitely works in PG as well.

... and starting with version 3.3.0, SQLite supports the PG syntax as well.

SQLite goes further, to provide a ​review of the feature in other databases -- MySQL/MariaDB apparently support it too, but use a slightly different syntax.

[Juan Pablo Garcia]

Hi, is this feature going to be added any time soon?

[Natalia Bidart]

Hello Juan Pablo,

Django is an open source project and the contributions come from community members, who work on tickets when they can, on what they can.

You are welcome to try to work on this if there is an urgency. The ​contributing guide is the best place to start.

Thanks!