Opened 8 years ago
Closed 7 years ago
#26634 closed New feature (fixed)
Add the ability to do subselects for querying versioned data
Reported by: | Darren Hobbs | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.8 |
Severity: | Normal | Keywords: | QuerySet.extra |
Cc: | Triage Stage: | Accepted | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
I have versioned data of the form:
id (autogenerate)
ref (uuid)
version (integer)
Each 'save' inserts a new version with the same ref and (version+1) version number.
I'd like an easy way to say 'give me the latest versions' (ie. distinct on refs, with max(version)).
The SQL looks like this:
select * from myapp_project where id in (SELECT id from myapp_project p WHERE p.version = (SELECT max(version) FROM myapp_project p1 WHERE p1.ref = p.ref))
I had to use extra(where=[maxids])
to achieve this*.
*Where maxids = "id in (SELECT id from myapp_project p WHERE p.version = (SELECT max(version) FROM myapp_project p1 WHERE p1.ref = p.ref))"
Stackoverflow discussion here:
http://stackoverflow.com/questions/37303010/what-is-the-django-way-of-doing-a-subselect
Change History (4)
comment:1 by , 8 years ago
Description: | modified (diff) |
---|
comment:2 by , 8 years ago
comment:3 by , 8 years ago
Component: | Uncategorized → Database layer (models, ORM) |
---|---|
Triage Stage: | Unreviewed → Accepted |
Type: | Uncategorized → New feature |
comment:4 by , 7 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
Per Simon's comment, perhaps this is addressed by #27149 -- Added Subquery and Exists database expressions. If not, feel free to reopen and explain what further enhancements are needed.
If your database supports
DISTINCT ON
your could use:I suppose this could be solved by the ungoing effort to add
SubQuery
support.