Opened 9 years ago

Closed 9 years ago

Last modified 13 days ago

#7070 closed (fixed)

Query.get_ordering doesn't support foreign key related ordering when combined with distinct()

Reported by: matt@… Owned by: nobody
Component: Uncategorized Version: queryset-refactor
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:


As per my comment on django-users:

If you have a query set which has had distinct() called and then order_by() on a foreign key
field you don't get the ordering on that foreign key field - the
resulting generated sql query has the joins for the foreign table
ready to be used for the order_by, but the "if not distinct or elt in
select_aliases" bit inside get_ordering() prevents the actual ORDER BY
portion being added.

The following example case illustrates. The models are:

from django.db import models

class FKTest(models.Model):
        name = models.CharField()

class Test(models.Model):
        fk = models.ForeignKey('FKTest')
        name = models.CharField()

Using those models:

print str(Test.objects.all().order_by('fk__name').query)
print str(Test.objects.all().order_by('fk__name').distinct().query)
print str(Test.objects.all().order_by('name').query)
print str(Test.objects.all().order_by('name').distinct().query)

This gives the output (with psycopg being my database driver):

SELECT "crs_test"."id", "crs_test"."fk_id", "crs_test"."name" FROM "crs_test" INNER JOIN "crs_fktest" ON ("crs_test"."fk_id" = "crs_fktest"."id") ORDER BY "crs_fktest"."name" ASC
SELECT DISTINCT "crs_test"."id", "crs_test"."fk_id", "crs_test"."name" FROM "crs_test" INNER JOIN "crs_fktest" ON ("crs_test"."fk_id" = "crs_fktest"."id")
SELECT "crs_test"."id", "crs_test"."fk_id", "crs_test"."name" FROM "crs_test" ORDER BY "crs_test"."name" ASC
SELECT DISTINCT "crs_test"."id", "crs_test"."fk_id", "crs_test"."name" FROM "crs_test" ORDER BY "crs_test"."name" ASC

As you can see the DISTINCT version of the sort on a foreign object's name is missing the ORDER BY clause.

Change History (2)

comment:1 Changed 9 years ago by Malcolm Tredinnick

Resolution: fixed
Status: newclosed

(In [7455]) queryset-refactor: Changed the way order_by() and distinct() interact.

When using "select distinct" all ordering columns must be part of the output
(select) columns. We were previously just throwing away ordering columns that
weren't included, but there are some cases where they are needed and it's
difficult to add them in manually. So now the default behaviour is to append
any missing columns.

This can affect the output of distinct() if complicated order_by() constructs
are used, so the documentation has been updated with an explanation of what's
going on there.

Fixed #7070.

comment:2 Changed 13 days ago by Tim Graham <timograham@…>

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset

In c754bdc:

Refs #7070 -- Improved test for extra(), values(), distinct() and ordering() all together.

Tested that the extra select that appears in the select clause only because of
distinct() and order_by() doesn't appear in the result values.

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