Cannot order_by on an annotated field

[GoogleCodeExporter]

Observed on rev 183

Given a couple of models like the following

class Person(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=50)

class PersonShoes(models.Model):
    id = models.AutoField(primary_key=True)
    person = models.ForeignKey(Person, related_name='shoes')
    brand = models.CharField(max_length=50)

If you wanted to query a list of people ordered by the number of shoes they own 
the django methodology for this would be:

peopleByShoeCount = 
Person.objects.annotate(num_shoes=Count('shoes')).order_by('num_shoes')

But when attempting this query with a django-pyodbc backend the following error 
is given:

[42S22] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 
'num_shoes'.

Original issue reported on code.google.com by Tojiro@gmail.com on 14 Dec 2010 at 5:11

[GoogleCodeExporter]

Quick correction. The error seems to only occur when attempting to slice the 
result set, like when attempting to get the top 10 results.

Person.objects.annotate(num_shoes=Count('shoes')).order_by('num_shoes')[:10]

Original comment by Tojiro@gmail.com on 14 Dec 2010 at 5:17

[GoogleCodeExporter]

Tojiro,

The problem is due to the fact that MS SQL doesn't allow you to use column 
aliases in the ORDER BY clause which is needed when you want to take a slice. 
Apply the attached patch to sql_server/pyodbc/query.py

This is an ugly patch but it works. 

Original comment by czamb...@gmail.com on 8 Jan 2011 at 12:16

Attachments:

• aggregate_patch.diff

[GoogleCodeExporter]

Original comment by vcc.ch...@gmail.com on 24 Mar 2011 at 4:53

• Changed state: Accepted