Opened 12 years ago

Last modified 10 years ago

#19195 closed Bug

Using distinct([*fields]) filter on a foreign key produces an ordering error when the foreign key has a Meta ordering field. — at Version 6

Reported by: chrisedgemon@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.7-beta-2
Severity: Release blocker Keywords: distinct, query
Cc: Simon Charette Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Simon Charette)

I tried to using a distinct filter like this: Appearance.objects.order_by('team').distinct('team'); this fails with the following Database Error: "DatabaseError: SELECT DISTINCT ON expressions must match initial ORDER BY expressions"

It is possible to work around this problem by using this modified filter: Appearance.objects.order_by('team__id').distinct('team__id').

Model definition: http://pastebin.com/index/J45fy9fr
Full traceback: http://pastebin.com/feSFMbzX

Change History (4)

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

Triage Stage: UnreviewedAccepted

The problem is that .distinct('team') can't do distinct on Team._meta.ordering as that will give unexpected results if the Team._meta.ordering isn't unique. In general, the user doesn't want that anyways. On the other hand we can't alter what .order_by('team') does. So, I think we have to disallow doing .distinct('team') if there is ordering defined for the related model.

The error should point to using .distinct('team_id').order_by('team_id') - though this syntax doesn't seem to work at the moment.

comment:2 by chrisedgemon@…, 12 years ago

There should actually be two underscores for the team_id filter - order_by fails on team_id.

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

I think we should allow using .order_by('team_id') here.

Related fields have two attributes on model level - team and team_id in this case. We allow using team_id in many places in the ORM already, and to me it seems we should allow it in order_by and distinct, too.

Is there some reason to *not* allow them?

comment:6 by Simon Charette, 11 years ago

Cc: Simon Charette added
Description: modified (diff)

Just hit this issue and had a hard time figuring out what I've done wrong.

Intuitively I tried .distinct('related_id').order_by('related_id') after realizing removing my Related._meta.ordering solved the issue but, as pointed out by akaariai, this is not allowed ATM.

Replying to akaariai:

I think we should allow using .order_by('team_id') here.

Related fields have two attributes on model level - team and team_id in this case. We allow using team_id in many places in the ORM already, and to me it seems we should allow it in order_by and distinct, too.

Is there some reason to *not* allow them?

I can't think of any reason we'd like *not* to allow them. It looks like sanest to expose an API to explicitly opt-out of the existing related model ordering behavior while maintaining backward compatibility.

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