Opened 14 years ago

Closed 13 years ago

#1517 closed defect (duplicate)

Query result ordering on field in different table broken when doing join on same table

Reported by: rfugger at gmail dot com Owned by: Adrian Holovaty
Component: Database layer (models, ORM) Version: 0.91
Severity: normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


(Using django 0.91/postgresql)

Doing a join on table X creates an alias like "t1" for table X in the SQL. If I then order_by='X.something', I get the following undesirable behaviour:

"The alias becomes the new name of the table reference for the current query — it is no longer possible to refer to the table by the original name. Thus

SELECT * FROM my_table AS m WHERE my_table.a > 5;

is not valid SQL syntax. What will actually happen (this is a PostgreSQL extension to the standard) is that an implicit table reference is added to the FROM clause, so the query is processed as if it were written as

SELECT * FROM my_table AS m, my_table AS my_table WHERE my_table.a > 5;

which will result in a cross join, which is usually not what you want."

Django must rename my order_by argument to account for the alias it has given the table. It doesn't appear to be doing this.

Change History (2)

comment:1 Changed 14 years ago by rfugger at gmail dot com

OK, I realize that I was pretty vague. Let me be specific:

I want to do this:

 links = paymentlinks.get_list(path__payment__date__gt=start, 
      payer_account__id__in=(, acct.partner_acct_id),

But that fails for the reason given on the link in the original comment. To make it work, I had to look at what SQL Django was outputting and hack it like this:

 links = paymentlinks.get_list(path__payment__date__gt=start, 
      payer_account__id__in=(, acct.partner_acct_id),

which I shouldn't have to do. Django should adjust order_by argument internally to its own SQL aliasing scheme. Hopefully that's a little more clear.

comment:2 Changed 13 years ago by Michael Radziej <mir@…>

Resolution: duplicate
Status: newclosed

duplicate of #2076

order_by('table.column') was never properly supported, documented is order_by('field1__field2'), which doesn't work either ;-)

But latter one is covered in #2076

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