Add the ability to do subselects for querying versioned data

[Darren Hobbs]

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

[Simon Charette]

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.

[Tim Graham]

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.