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

[rfugger at gmail dot com]

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

[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.id, acct.partner_acct_id),
      order_by=('-ripple_payments.date',)) 

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.id, acct.partner_acct_id),
      order_by=('-t2.date',)) 

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.

[Michael Radziej <mir@…>]

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