Orderable ArrayAgg and StringAgg

[Floris den Hengst]

The Postgres-specific ArrayAgg and StringAgg aggregations were added in ​Django 1.9.

The ​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:

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:

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 ​mailinglist discussion for this feature, any ordering added within may need to be contributed to GROUP BY. This might require some investigation.

[Floris den Hengst]

As pointed out by Josh Smeaton in the mailing list ​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' ​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.

[Floris den Hengst]

PR here: ​https://github.com/django/django/pull/6886

[Tim Graham]

Comments for improvement are on the PR.

[Floris den Hengst]

Replying to 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

[Tim Graham]

Patch needs rebasing and targeting for Django 2.0.

[Floris den Hengst]

Unset patch new improvement after rebasing and targeting for Django 2.0.

[Tim Graham <timograham@…>]

In 96199e5:

Fixed #26067 -- Added ordering support to ArrayAgg and StringAgg.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 47a1f2a0:

Refs #26067 -- Added more tests for ordering in StringAgg.