Opened 3 years ago

Closed 7 months ago

#26067 closed New feature (fixed)

Orderable ArrayAgg and StringAgg

Reported by: Floris den Hengst Owned by: Floris den Hengst
Component: contrib.postgres Version: master
Severity: Normal Keywords: ArrayAgg StringAgg ordering
Cc: Matthew Pava Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description (last modified by 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.

Change History (13)

comment:1 Changed 3 years ago by Floris den Hengst

Description: modified (diff)

comment:2 Changed 3 years ago by Tim Graham

Triage Stage: UnreviewedAccepted
Version: master

comment:3 Changed 3 years ago by Floris den Hengst

Owner: set to Floris den Hengst
Status: newassigned

comment:4 Changed 3 years ago by 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.

comment:5 Changed 3 years ago by Floris den Hengst

Has patch: set
Last edited 3 years ago by Floris den Hengst (previous) (diff)

comment:6 Changed 3 years ago by Tim Graham

Patch needs improvement: set

Comments for improvement are on the PR.

comment:7 in reply to:  6 Changed 2 years ago by 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

comment:8 Changed 2 years ago by Floris den Hengst

Patch needs improvement: unset

comment:9 Changed 2 years ago by Tim Graham

Patch needs improvement: set

Patch needs rebasing and targeting for Django 2.0.

comment:10 Changed 2 years ago by Floris den Hengst

Patch needs improvement: unset

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

comment:11 Changed 15 months ago by Tim Graham

Patch needs improvement: set

comment:12 Changed 9 months ago by Matthew Pava

Cc: Matthew Pava added

comment:13 Changed 7 months ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In 96199e5:

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

Note: See TracTickets for help on using tickets.
Back to Top