Opened 7 years ago

Closed 7 years ago

#6373 closed (duplicate)

Ordering by ForeignKey does not work

Reported by: zavood Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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
);

Change History (5)

comment:1 Changed 7 years ago by zavood

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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;

comment:2 Changed 7 years ago by anonymous

  • Component changed from Database wrapper to Admin interface

comment:3 Changed 7 years ago by 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.

comment:4 Changed 7 years ago by anonymous

  • Component changed from Admin interface to Database wrapper

comment:5 Changed 7 years ago by ramiro

  • Resolution set to duplicate
  • Status changed from new to closed

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

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