The example on ordering a queryset by a field in a different table in the development documentation on queryset-api (http://docs.djangoproject.com/en/dev/ref/models/querysets/#queryset-api) offers the following syntax:
Entry.objects.order_by('blogs_blog.name', 'headline')
preceded by the following explanation: "To order by a field in a different table, add the other table’s name and a dot, like so:"
For the following model definitions
from django.db import models
class Poll(models.Model):
question = models.CharField(max_length=200)
pub_date = models.DateTimeField('date published')
def __unicode__(self):
return self.question
class Choice(models.Model):
poll = models.ForeignKey(Poll)
choices = models.CharField(max_length=200)
votes = models.IntegerField()
def __unicode__(self):
return self.choices
with MySQL, for this retrieval example:
c=Choice.objects.order_by("polls_poll.question",'-votes')
list(c)
the result is "OperationalError?: (1054, "Unknown column 'polls_poll.question' in 'order clause'" in reply to the following SQL query generated:
SELECT `polls_choice`.`id`, `polls_choice`.`poll_id`, `polls_choice`.`choices`,
`polls_choice`.`votes` FROM `polls_choice` ORDER BY `polls_poll`.question ASC,
`polls_choice`.`votes` DESC
On the other hand
if one follows the syntax offered in the "mainstream" documentation (http://www.djandoproject.com/db-api), i.e.,
c=Choice.objects.order_by("poll__question",'-votes')
list(c)
things do work, with the following SQL query generated:
SELECT `polls_choice`.`id`, `polls_choice`.`poll_id`, `polls_choice`.`choices`,
`polls_choice`.`votes` FROM `polls_choice` INNER JOIN `polls_poll`
ON (`polls_choice`.`poll_id` = `polls_poll`.`id`) ORDER BY `polls_poll`.`question` ASC,
`polls_choice`.`votes` DESC
This problem might be related to the one reported in the accepted ticket #2884 (component: Admin interface), solution for which is said to be deferred until after queryset-refactor.
