Description: Under certain circumstances, Model.objects.select_related().order_by('table.field') will generate strangely ordered output. As a result, the admin interface displays incorrectly sorted items when the heading of a column specified in list_display is clicked.
How to reproduce: This only occurs when the model being queried contains multiple foreign keys which point to the same table, one directly and another indirectly via other ForeignKey relationships. Example:
from django.db import models
class Program(models.Model):
name = models.CharField(maxlength=30)
class Admin:
pass
def __str__(self):
return self.name
class Teacher(models.Model):
first_name = models.CharField(maxlength=20)
last_name = models.CharField(maxlength=20)
def __str__(self):
return self.last_name +", "+ self.first_name
class Admin:
pass
class Meta:
ordering = ["last_name"]
class Department(models.Model):
name = models.CharField(maxlength=20)
head = models.ForeignKey(Teacher)
def __str__(self):
return self.name
class Admin:
pass
class Subject(models.Model):
name = models.CharField(maxlength=40)
department = models.ForeignKey(Department)
def __str__(self):
return self.name
class Admin:
pass
class Course(models.Model):
program = models.ForeignKey(Program,default=1)
subject = models.ForeignKey(Subject)
teacher = models.ForeignKey(Teacher)
def __str__(self):
return str(self.id)
class Admin:
list_display = ('__str__','program','subject','teacher')
The 'model in question' here is Course. Following the relationships recursively in order:
Course.program -> Progam
Course.subject -> Subject -> Department -> Teacher
Course.teacher -> Teacher
i.e. the Teacher model is reached more than once when recursively following ForeignKey relationships. Also necessary to reproduce the bug is the fact that the the ForeignKey pointing directly to Teacher is reached AFTER having followed the indirect link to Teacher.
Using this model and browsing to the admin page for Courses, clicking on the Teacher column will not sort the teacher's names correctly. In fact, they are sorted by the name of the course's head of department, which is not (and cannot be) shown on the admin page.
Cause: This is a subtle bug in django/db/models/query.py: when recursively following ForeignKey relationships in the fill_table_cache function, a table that appears multiple times in a join is given an alias for the second (and subsequent) times it appears in the SQL statement. In the situation described above, the alias is assigned to the join for the 'more important' relationship, i.e. the direct course -> teacher. However, the ORDER BY statement is left unchanged as 'ORDER BY appname_teacher.last_name', which actually contains the course's head of department.
Temporary Solution: Changing the order in which the model's fields are ordered so that the 'direct' ForeignKey is before the 'indirect' one. For example, changing the Course model above to:
class Course(models.Model):
teacher = models.ForeignKey(Teacher)
program = models.ForeignKey(Program,default=1)
subject = models.ForeignKey(Subject)
def __str__(self):
return str(self.id)
class Admin:
list_display = ('__str__','program','subject','teacher')
will now show the correct sorting in the admin.
I'm trying to work on a patch, but I'm close to giving up. I'm not a very experienced programmer, and IMHO this is a sticky one :)
-Basil
