[Django] #26067: Orderable ArrayAgg and StringAgg
Django
---------------------------------+-----------------------------------------
Reporter: fdh | Owner:
Type: New feature | Status: new
Component: | Version:
contrib.postgres |
Severity: Normal | Keywords: ArrayAgg StringAgg ordering
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
---------------------------------+-----------------------------------------
The [http://www.postgresql.org/docs/9.0/static/functions-aggregate.html
documentation] of Postgres 9.0 first mentions the possibility of ordering
the results within aggregations such as ARRAY_AGG and STRING_AGG.
This could be useful in some cases.
For example: it could make sense to perform a StringAgg in lexicographical
order in some cases.
The basic format of ordering within aggregations in SQL is quite simple:
{{{
#!sql
SELECT ARRAY_AGG(some_field ORDER BY some_field ASC) FROM table;
SELECT ARRAY_AGG(some_field ORDER BY some_field DESC) FROM table;
SELECT ARRAY_AGG(some_field ORDER BY other_field ASC) FROM table;
}}}
It would be nice if the above would be supported as follows:
{{{
#!python
Model.objects.aggregate(ArrayAgg(some_field, order_by='some_field'))
Model.objects.aggregate(ArrayAgg(some_field, order_by='-some_field'))
Model.objects.aggregate(ArrayAgg(some_field, order_by='other_field'))
}}}
where order_by is an optional parameter. If it not specified, behavior can
remain unchanged from the current implementation.
As noted by Josh Smeaton in the [https://groups.google.com/forum/#!topic
/django-developers/YCSDX3GApBM mailinglist] discussion for this feature,
any ordering added within '''may''' need to be contributed to GROUP BY.
This might require some investigation.
--
Ticket URL: <https://code.djangoproject.com/ticket/26067>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: fdh | Owner:
Type: New feature | Status: new
Severity: Normal | Resolution:
Keywords: ArrayAgg StringAgg | Triage Stage:
ordering | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by fdh):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
> The [http://www.postgresql.org/docs/9.0/static/functions-aggregate.html
> documentation] of Postgres 9.0 first mentions the possibility of ordering
> the results within aggregations such as ARRAY_AGG and STRING_AGG.
>
> This could be useful in some cases.
> For example: it could make sense to perform a StringAgg in
> lexicographical order in some cases.
>
> The basic format of ordering within aggregations in SQL is quite simple:
> {{{
> #!sql
> SELECT ARRAY_AGG(some_field ORDER BY some_field ASC) FROM table;
> SELECT ARRAY_AGG(some_field ORDER BY some_field DESC) FROM table;
> SELECT ARRAY_AGG(some_field ORDER BY other_field ASC) FROM table;
> }}}
>
> It would be nice if the above would be supported as follows:
> {{{
> #!python
> Model.objects.aggregate(ArrayAgg(some_field, order_by='some_field'))
> Model.objects.aggregate(ArrayAgg(some_field, order_by='-some_field'))
> Model.objects.aggregate(ArrayAgg(some_field, order_by='other_field'))
> }}}
>
> where order_by is an optional parameter. If it not specified, behavior
> can remain unchanged from the current implementation.
>
> As noted by Josh Smeaton in the [https://groups.google.com/forum/#!topic
> /django-developers/YCSDX3GApBM mailinglist] discussion for this feature,
> any ordering added within '''may''' need to be contributed to GROUP BY.
> This might require some investigation.
New description:
The Postgres-specific ArrayAgg and StringAgg aggregations were added in
[https://docs.djangoproject.com/en/1.9/ref/contrib/postgres/aggregates/
Django 1.9].
The [http://www.postgresql.org/docs/9.0/static/functions-aggregate.html
documentation] of Postgres 9.0 first mentions the possibility of ordering
the results within aggregations such as ARRAY_AGG and STRING_AGG.
This could be useful in some cases.
For example: it could make sense to perform a StringAgg in lexicographical
order in some cases.
The basic format of ordering within aggregations in SQL is quite simple:
{{{
#!sql
SELECT ARRAY_AGG(some_field ORDER BY some_field ASC) FROM table;
SELECT ARRAY_AGG(some_field ORDER BY some_field DESC) FROM table;
SELECT ARRAY_AGG(some_field ORDER BY other_field ASC) FROM table;
}}}
It would be nice if the above would be supported as follows:
{{{
#!python
Model.objects.aggregate(ArrayAgg(some_field, order_by='some_field'))
Model.objects.aggregate(ArrayAgg(some_field, order_by='-some_field'))
Model.objects.aggregate(ArrayAgg(some_field, order_by='other_field'))
}}}
where order_by is an optional parameter. If it not specified, behavior can
remain unchanged from the current implementation.
As noted by Josh Smeaton in the [https://groups.google.com/forum/#!topic
/django-developers/YCSDX3GApBM mailinglist] discussion for this feature,
any ordering added within '''may''' need to be contributed to GROUP BY.
This might require some investigation.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/26067#comment:1>
Django
Reporter: fdh | Owner:
Type: New feature | Status: new
Severity: Normal | Resolution:
Keywords: ArrayAgg StringAgg | Triage Stage: Accepted
ordering |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* version: => master
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/26067#comment:2>
Django
Reporter: fdh | Owner: fdh
Type: New feature | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: ArrayAgg StringAgg | Triage Stage: Accepted
ordering |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by fdh):
* owner: => fdh
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/26067#comment:3>
Django
Reporter: fdh | Owner: fdh
Type: New feature | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: ArrayAgg StringAgg | Triage Stage: Accepted
ordering |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by fdh):
As pointed out by Josh Smeaton in the mailing list
[https://groups.google.com/forum/#!topic/django-developers/YCSDX3GApBM
discussion]:
Consider that any ordering added within *may* need to be contributed to
GROUP BY, but I haven't read the documentation to say that is a
requirement.
In the Postgres' [https://www.postgresql.org/docs/9.5/static/functions-
aggregate.html documentation] these aggregations are only mentioned as
being special by meaningful difference in ordering input values:
The aggregate functions array_agg, [...] string_agg, and xmlagg, as well
as similar user-defined aggregate functions, produce meaningfully
different result values depending on the order of the input values. This
ordering is unspecified by default, but can be controlled by writing an
ORDER BY clause within the aggregate call, as shown in Section 4.2.7.
I don't think anything in the ORDER BY clause in the aggregation would
have to be contributed to a GROUP BY clause in the surrounding query as
the aggregation operation is independent of the grouping of the
surrounding query.
--
Ticket URL: <https://code.djangoproject.com/ticket/26067#comment:4>
Django
Reporter: fdh | Owner: fdh
Type: New feature | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: ArrayAgg StringAgg | Triage Stage: Accepted
ordering |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by fdh):
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/26067#comment:5>
Django
Reporter: fdh | Owner: fdh
Type: New feature | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: ArrayAgg StringAgg | Triage Stage: Accepted
ordering |
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* needs_better_patch: 0 => 1
Comment:
Comments for improvement are on the PR.
--
Ticket URL: <https://code.djangoproject.com/ticket/26067#comment:6>
Django
Reporter: Floris den Hengst | Owner: Floris
| den Hengst
Type: New feature | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: ArrayAgg StringAgg | Triage Stage: Accepted
ordering |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Floris den Hengst):
Replying to [comment:6 Tim Graham]:
> Comments for improvement are on the PR.
The original PR was closed due to inactivity.
A new PR (in which the proposed improvements have been included) can be
found here: https://github.com/django/django/pull/7604
--
Ticket URL: <https://code.djangoproject.com/ticket/26067#comment:7>
Django
Reporter: Floris den Hengst | Owner: Floris
| den Hengst
Type: New feature | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: ArrayAgg StringAgg | Triage Stage: Accepted
ordering |
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Floris den Hengst):
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/26067#comment:8>
Django
Reporter: Floris den Hengst | Owner: Floris
| den Hengst
Type: New feature | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: ArrayAgg StringAgg | Triage Stage: Accepted
ordering |
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* needs_better_patch: 0 => 1
Comment:
Patch needs rebasing and targeting for Django 2.0.
--
Ticket URL: <https://code.djangoproject.com/ticket/26067#comment:9>
Django
Reporter: Floris den Hengst | Owner: Floris
| den Hengst
Type: New feature | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: ArrayAgg StringAgg | Triage Stage: Accepted
ordering |
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Floris den Hengst):
* needs_better_patch: 1 => 0
Comment:
Unset patch new improvement after rebasing and targeting for Django 2.0.
--
Ticket URL: <https://code.djangoproject.com/ticket/26067#comment:10>
Django
Reporter: Floris den Hengst | Owner: Floris
| den Hengst
Type: New feature | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: ArrayAgg StringAgg | Triage Stage: Accepted
ordering |
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/26067#comment:11>
Django
Reporter: Floris den Hengst | Owner: Floris
| den Hengst
Type: New feature | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: ArrayAgg StringAgg | Triage Stage: Accepted
ordering |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by Matthew Pava):
* cc: Matthew Pava (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/26067#comment:12>
Django
Reporter: Floris den Hengst | Owner: Floris
| den Hengst
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: fixed
Keywords: ArrayAgg StringAgg | Triage Stage: Accepted
ordering |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by Tim Graham <timograham@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"96199e562dcc409ab4bdc2b2146fa7cf73c7c5fe" 96199e5]:
{{{
#!CommitTicketReference repository=""
revision="96199e562dcc409ab4bdc2b2146fa7cf73c7c5fe"
Fixed #26067 -- Added ordering support to ArrayAgg and StringAgg.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26067#comment:13>
Django
Reporter: Floris den Hengst | Owner: Floris
| den Hengst
Type: New feature | Status: closed
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: fixed
Keywords: ArrayAgg StringAgg | Triage Stage: Accepted
ordering |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"47a1f2a06fac2ee88d3ba9d88e8f7c45a4f6f5e8" 47a1f2a0]:
{{{
#!CommitTicketReference repository=""
revision="47a1f2a06fac2ee88d3ba9d88e8f7c45a4f6f5e8"
Refs #26067 -- Added more tests for ordering in StringAgg.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26067#comment:14>