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

[chrisedgemon@…]

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

[Anssi Kääriäinen]

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.

[chrisedgemon@…]

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

[Anssi Kääriäinen]

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?

[Simon Charette]

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.