#25643: Allow update() with aggregates and joins via subqueries
-------------------------------------+-------------------------------------
Reporter: jorgecarleitao | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: dev
(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
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
> and starting with version 3.3.0, SQLite supports the PG syntax as well.
Small admonition it's SQLite 3.33.0 and not 3.3.0
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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/010701960e547f96-e23d5368-4619-4aea-9ecd-80d87fada48b-000000%40eu-central-1.amazonses.com.
