Code

Opened 8 years ago

Closed 7 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
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: UI/UX:

Description

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

Attachments (0)

Change History (2)

comment:1 Changed 8 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.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.

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

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

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.