Opened 5 years ago

Closed 4 years ago

Last modified 2 years ago

#14139 closed (duplicate)

Feature Request: distinct() should support field names

Reported by: mjs7231 Owned by:
Component: Database layer (models, ORM) Version: 1.2
Severity: Keywords:
Cc: Triage Stage: Design decision needed
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 below. However the resulting query adds parens around the extra() clause:

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

According to bug #1413, this is the wrong approach. A better solution is that distinct() should support field names:

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.

Change History (6)

comment:1 Changed 5 years ago by mjs7231

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

Sorry, its in reference to Ticket #14137, not the one mentioned above.

comment:2 Changed 5 years ago by anonymous

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

I don't think the distinct method should support keywords. The query in your post there can be done with values() and annotate(). Also, I think adding something like this would make querysets more complicated. Plus, I think this has come up before.

comment:3 Changed 5 years ago by lrekucki

  • Resolution invalid deleted
  • Status changed from closed to reopened
  • Triage Stage changed from Unreviewed to Design decision needed

I'm going to re-open this:

1) I think this is a valid feature request. A query like "select this items, but make them distinct on attribute X instead of all attributes", seems reasonable and SQL agnostic.

2) If this can easily be done using current methods, then it's at least worth documenting.

comment:4 Changed 5 years ago by anonymous

+1

comment:5 Changed 4 years ago by ikelly

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

This is a duplicate of #6422.

comment:6 Changed 2 years ago by akaariai

  • Component changed from ORM aggregation to Database layer (models, ORM)
Note: See TracTickets for help on using tickets.
Back to Top