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
6708	.order_by works ugly with JOIN	Artem Skoretskiy	nobody	"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()`)."		closed	Uncategorized	dev		duplicate		tonn81@…	Unreviewed	0	0	0	0	0	0