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 Simon Charette)

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 Simon Charette, 8 years ago

Description: modified (diff)

comment:2 by Simon Charette, 8 years ago

If your database supports DISTINCT ON your could use:

latest_project_versions = Project.objects.filter(
    pk__in=Project.objects.order_by('ref', '-version').distinct('ref'),
)

I suppose this could be solved by the ungoing effort to add SubQuery support.

comment:3 by Tim Graham, 8 years ago

Component: UncategorizedDatabase layer (models, ORM)
Triage Stage: UnreviewedAccepted
Type: UncategorizedNew feature

comment:4 by Tim Graham, 7 years ago

Resolution: fixed
Status: newclosed

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.

Note: See TracTickets for help on using tickets.
Back to Top