Opened 17 years ago

Closed 17 years ago

Last modified 17 years ago

#6708 closed (duplicate)

.order_by works ugly with JOIN

Reported by: Artem Skoretskiy Owned by: nobody
Component: Uncategorized Version: dev
Severity: Keywords:
Cc: tonn81@… Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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()).

Change History (5)

comment:1 by tonnzor <tonn81@…>, 17 years ago

Cc: Artem Skoretskiy <tonn81@…> added

comment:2 by tonnzor <tonn81@…>, 17 years ago

Cc: Artem Skoretskiy removed

comment:3 by tonnzor <tonn81@…>, 17 years ago

Cc: tonn81@… added; <tonn81@…> removed

comment:4 by Ramiro Morales, 17 years ago

Resolution: duplicate
Status: newclosed

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.

comment:5 by Malcolm Tredinnick, 17 years ago

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

Note: See TracTickets for help on using tickets.
Back to Top