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: | 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 )
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 , 12 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:2 by , 12 years ago
There should actually be two underscores for the team_id filter - order_by fails on team_id.
comment:3 by , 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 , 11 years ago
Cc: | 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.
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.