Opened 2 years ago

Last modified 9 months ago

#25643 new New feature

Allow update() with aggregates and joins via subqueries

Reported by: jorgecarleitao Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Marc Tamlyn Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (4)

comment:1 Changed 2 years ago by Josh Smeaton

Summary: Extend expressions API to update()Allow update() with aggregates and joins via subqueries
Triage Stage: UnreviewedAccepted

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.

comment:2 Changed 2 years ago by 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.

comment:3 Changed 17 months ago by Marc Tamlyn

Cc: Marc Tamlyn added

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

comment:4 Changed 9 months ago by 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.

Note: See TracTickets for help on using tickets.
Back to Top