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 , 9 years ago
Summary: | Extend expressions API to update() → Allow update() with aggregates and joins via subqueries |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:2 by , 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.
follow-up: 5 comment:3 by , 8 years ago
Cc: | added |
---|
Agreed this would be great. For what it's work, update .. from ..
definitely works in PG as well.
comment:4 by , 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.
comment:5 by , 3 years ago
Cc: | 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:7 by , 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!
Expressions work in
update()
and now alsocreate()
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 anUPDATE .. 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.