id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
2874	select_related (and hence the admin) sometimes generate incorrect ordering	bangcok_dangerus [ a t ) hotmail.com	nobody	"'''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[[BR]]
Course.subject -> Subject -> Department -> Teacher[[BR]]
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
"	defect	closed	Database layer (models, ORM)	dev	normal	fixed	qs-rf-fixed		Accepted	1	0	0	0	0	0