Opened 9 years ago

Last modified 16 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: dev
Severity: Normal Keywords:
Cc: Marc Tamlyn, Shai Berger 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 (7)

comment:1 by Josh Smeaton, 9 years ago

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 by Anssi Kääriäinen, 9 years ago

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 by Marc Tamlyn, 8 years ago

Cc: Marc Tamlyn added

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

comment:4 by Mads Jensen, 8 years ago

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

in reply to:  3 comment:5 by Shai Berger, 3 years ago

Cc: Shai Berger added

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.

comment:6 by Juan Pablo Garcia, 16 months ago

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

comment:7 by Natalia Bidart, 16 months ago

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!

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