Ordering by ForeignKey does not work

[zavood]

Prepare a clean Django project:

$ django-admin.py startproject testsqlite
$ cd testsqlite
$ ./manage.py startapp testapp

Add the following to settings.py:

import os
PROJDIR = os.path.dirname(os.path.abspath(__file__))
DATABASE_ENGINE = 'sqlite3'
DATABASE_NAME = PROJDIR + '/test.db'
INSTALLED_APPS = (
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.sites',
    'django.contrib.admin',
    'testapp'
)

Enable admin in urls.py:

from django.conf.urls.defaults import *

urlpatterns = patterns('',
     (r'^admin/', include('django.contrib.admin.urls')),
)

Add the following model:

from django.db import models

class Foo(models.Model):
    label = models.CharField(max_length=10)
    number = models.PositiveIntegerField(unique=True)
    class Admin:
        pass
    class Meta:
        ordering = ['number']

class Bar(models.Model):
    foo = models.ForeignKey(Foo)
    class Meta:
        ordering = ['foo']

Load the schema and run server:

./manage.py syncdb
./manage.py runserver

1. go to ​http://127.0.0.1:8000/admin/
2. add one Foo object
3. try to add a Bar object, resulting in the following error:

   OperationalError at /admin/testapp/bar/
   no such column: testapp_foo.number
   

This message is incorrect:

$ sqlite3 test.db 
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> .schema testapp_foo
CREATE TABLE "testapp_foo" (
    "id" integer NOT NULL PRIMARY KEY,
    "label" varchar(10) NOT NULL,
    "number" integer unsigned NOT NULL UNIQUE
);

[zavood]

The problem is that select doesn't use the foreign key table.

Current, erroneous SQL:

SELECT "testapp_bar"."id","testapp_bar"."foo_id" FROM "testapp_bar" ORDER BY "testapp_foo"."id" ASC

Correct SQL:

SELECT "testapp_bar"."id","testapp_bar"."foo_id" FROM "testapp_bar", "testapp_foo" ORDER BY "testapp_foo"."number" ASC;

[anonymous]

The problem lies in django/contrib/admin/views/main.py, line 739.

For some reason, QuerySet.order_by() does not honour the behaviour documented in ​http://www.djangoproject.com/documentation/db-api/#order-by-fields , namely:

To order by a field in a different table, add the other table’s name and a dot.

Although the parameter it is called with, qs = qs.order_by('testapp_foo.number'), should be perfectly valid, it produces the error above by not including the testapp_foo table for some reason.

Looks like a serious bug.

[Ramiro Morales]

Duplicate of #2076 which has been solved on the queryser refactor branch.