Opened 8 years ago

Closed 8 years ago

Last modified 8 years ago

#6708 closed (duplicate)

.order_by works ugly with JOIN

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

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 Changed 8 years ago by tonnzor <tonn81@…>

  • Cc tonnzor <tonn81@…> added
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

comment:2 Changed 8 years ago by tonnzor <tonn81@…>

  • Cc tonnzor removed

comment:3 Changed 8 years ago by tonnzor <tonn81@…>

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

comment:4 Changed 8 years ago by ramiro

  • Resolution set to duplicate
  • Status changed from new to closed

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 Changed 8 years ago by mtredinnick

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