[Django] #25643: Extend expressions API to update()
Django
----------------------------------------------+--------------------
Reporter: jorgecarleitao | Owner: nobody
Type: New feature | Status: new
Component: Database layer (models, ORM) | Version: master
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
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 [http://stackoverflow.com/questions/3652736/django-update-
queryset-with-annotation this question] in SO.
--
Ticket URL: <https://code.djangoproject.com/ticket/25643>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: jorgecarleitao | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by jarshwah):
* needs_docs: => 0
* needs_better_patch: => 0
* needs_tests: => 0
* stage: Unreviewed => Accepted
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25643#comment:1>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by akaariai):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25643#comment:2>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by mjtamlyn):
* cc: mjtamlyn (added)
Comment:
Agreed this would be great. For what it's work, `update .. from ..`
definitely works in PG as well.
--
Ticket URL: <https://code.djangoproject.com/ticket/25643#comment:3>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25643#comment:4>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Shai Berger):
* cc: Shai Berger (added)
Comment:
Replying to [comment:3 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
[https://www.sqlite.org/lang_update.html#update_from_in_other_sql_database_engines
review of the feature in other databases] -- MySQL/MariaDB apparently
support it too, but use a slightly different syntax.
--
Ticket URL: <https://code.djangoproject.com/ticket/25643#comment:5>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by JPGarCar):
Hi, is this feature going to be added any time soon?
--
Ticket URL: <https://code.djangoproject.com/ticket/25643#comment:6>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by 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
[https://docs.djangoproject.com/en/dev/internals/contributing/writing-
code/ contributing guide] is the best place to start.
Thanks!
--
Ticket URL: <https://code.djangoproject.com/ticket/25643#comment:7>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Comment (by Simon Charette):
> and starting with version 3.3.0, SQLite supports the PG syntax as well.
For the record, ticket:36213#comment:8 includes a complete MySQL specific
implementation of `UPDATE FROM` that could likely be adapted to be
`features.supports_update_from` based instead and pave the way for fixing
this ticket. With generic `UPDATE FROM` support queries of the following
form to support aggregation would be trivial to implement
{{{#!sql
UPDATE relation
SET quantity = subquery.total_rating
FROM (SELECT relation_id, sum(rating) AS total_rating FROM signrelation
GROUP BY 1) AS subquery
WHERE subquery.relation_id = relation.id
}}}
For references the current update query compiler defaults to doing
{{{#!sql
UPDATE relation
SET quantity = ...
WHERE relation_id IN (
SELECT relation.id
FROM relation
JOIN ...
)
}}}
the moment a relationship is referenced which prevents the usage of
aggregation, window functions, or any reference to other table columns
really. I believe that reason why it was implemented this way is just that
the non-standard `UPDATE FROM` syntax was not prevalent at the time but
now that it's supported on SQLite, Postgres, and MySQL there's
[https://forum.djangoproject.com/t/queryset-update-silently-turns-into-
select-update-mysql/39095/7 a strong case for implementing it].
--
Ticket URL: <https://code.djangoproject.com/ticket/25643#comment:8>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Changes (by Simon Charette):
* cc: Simon Charette (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/25643#comment:9>