Code

Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#14137 closed (invalid)

Django ORM adding unwanted parens when using extra()

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

Description

I have a table REPORTS containing the columns {id, user, date}. I was attempting to get the row with the largest date for each user. Using Django's ORM I can pretty much get what I want using the queryset definition:

queryset = Report.objects.extra(select={'lastid':"DISTINCT ON (user_id) id"})
queryset = queryset.order_by('user', '-date')
queryset = queryset.values('lastid')

This results in the query:

SELECT (DISTINCT ON (user_id) id) AS "lastid" FROM "report" ORDER BY "report"."user_id" ASC, "report"."date" DESC;

Postgres spits out an exception with this query. However, if you remove the extra parens around the DISTINCT clause, it works fine. So the query should really look like:

SELECT DISTINCT ON (user_id) id AS "lastid" FROM "report" ORDER BY "report"."user_id" ASC, "report"."date" DESC;

Attachments (0)

Change History (3)

comment:1 Changed 4 years ago by mjs7231

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Version changed from 1.2 to 1.1

comment:2 Changed 4 years ago by lrekucki

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

The "select" keyword in extra() should be used to select extra columns which can't be expressed in ORM (like complex expressions, using stored procedures, stuff like that) not to inject arbitrary SQL into a query. A better solution would be to support field names in distinct(), but that's a diffrent thing. After all, there is no reason why the ORM wouldn't put extra select columns on the end, which would also break your code.

comment:3 Changed 4 years ago by mjs7231

OK, filed a Feature Request with Ticket #14139.

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.