.order_by works ugly with JOIN

[Artem Skoretskiy]

When .order_by is used with join, table names are unexpected. Example is below.

money_manager/models.py:

# application name is 'money_manager'

class Operation(models.Model):
    date        = models.DateField()
    goods_name  = models.CharField(max_length=100, blank=True)

class OperationMoney(models.Model):
    operation   = models.ForeignKey(Operation, edit_inline=models.TABULAR, num_in_admin=3)
    ammount     = models.IntegerField(core=True)

Now let's try to get all OperationMoney objects starting 2008-01-01 and ordered by date and operation.id:

# 1. Naive solution
OperationMoney.objects.all().filter(operation__date__lte='2008-01-01').order_by('-operation.date','-operation')
#=> OperationalError: no such column: operation.date

# 2. Solution inspired by documentation (http://www.djangoproject.com/documentation/db-api/#order-by-fields)
OperationMoney.objects.all().filter(operation__date__lte='2008-01-01').order_by('-money_manager_operation.date','-operation')
#=> OperationalError: no such column: money_manager_operation.date

# 3. Solution inspired by .filter()
OperationMoney.objects.all().filter(operation__date__lte='2008-01-01').order_by('-operation__date','-operation')
#=> OperationalError: no such column: money_manager_operationmoney.operation__date

# 4. Combination of #2 and #4
OperationMoney.objects.all().filter(operation__date__lte='2008-01-01').order_by('-money_manager_operation__date','-operation')
#=> OperationalError: no such column: money_manager_operationmoney.money_manager_operation__date

# 5. Debugged result SQL and used table names from it
OperationMoney.objects.all().filter(operation__date__lte='2008-01-01').order_by('-money_manager_operationmoney__operation.date','-operation')
#=> <list of OperationMoney>

As you see, only #5 (ugliest and the most non-intuitive) works.

IMHO, #3 is the most intuitive (because all of us used .filter()).

[Ramiro Morales]

tonnzor, take a look at the quersyset refactor branch, where this is already fixed ([6510]), the fix will be brought to trunk hopefully soon. Also, had you searched the Trac tickets or the mailing list archives you would have found at least two reports of this type a week :). The ticket being used as the master ticket for this is #2076.

[Malcolm Tredinnick]

By the way, the current (trunk) order_by syntax for related models is documented in the db-api documentation.