Opened 16 years ago

Closed 16 years ago

Last modified 12 years ago

#10574 closed (fixed)

Remove unnecessary ordering in values() queries

Reported by: Malcolm Tredinnick Owned by: Malcolm Tredinnick
Component: Database layer (models, ORM) Version: dev
Severity: Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

The ORM applies any ordering (such as default ordering given by Meta.ordering) all the time. For this model:

class Item(models.Model):
    name = models.CharField(max_length=10)
    data = models.IntegerField()

    class Meta:
        ordering = ["name"]

This is redundant in values() queries like this:

Item.objects.values("data")

Even worse, it leads to incorrect results in aggregate queries like this one:

Item.objects.values("data").annotate(Count("id"))

Normally, that would return the number of entries for each data value. However, due to the ordering by name being included, we group by (data, name), which affects the result. The solution is to realise that the ordering columns aren't going to feature in the result and remove them.

It can be worked around, once you realise the problem, although that last part isn't easy (it took me more than a few minutes):

Items.objects.values("data").annotate(Count("id")).order_by()

but Django has enough information to work this out itself.

(I'm going to put this in the aggregation category, since that's where the real apparent bug emerges and people will be looking there for similar things, I guess.)

Change History (5)

comment:1 by Malcolm Tredinnick, 16 years ago

milestone: 1.1
Triage Stage: UnreviewedAccepted
Version: SVN

comment:2 by Malcolm Tredinnick, 16 years ago

I'm going to document our way out of this one so that Django's behaviour is consistent (if not brilliant). We never remove ordering constraints internally, it's always up to the user.

comment:3 by Malcolm Tredinnick, 16 years ago

Resolution: fixed
Status: newclosed

(In [10172]) Fixed #10574 -- Documented interaction between annotations and order_by.

In the future, I'd like to fix this properly, but the current behavior
has the advantage of being consistent across the board (and changing it
everywhere is backwards-incompatible with documented functionality).

comment:4 by Jacob, 13 years ago

milestone: 1.1

Milestone 1.1 deleted

comment:5 by Anssi Kääriäinen, 12 years ago

Component: ORM aggregationDatabase layer (models, ORM)
Note: See TracTickets for help on using tickets.
Back to Top