What is the django way of doing a subselect?

Question

I have a versioned model which (simplified) looks a bit like:

Project(id, ref, version)

unique_together(ref, version)

Where id is the autogenerated primary key, ref is a random UUID and version is an integer incremented by my application. Each time I save the project I create a new instance, add 1 to the version and copy the ref to the new object.

The following SQL will give me back the latest version of every Project, by doing a subselect.

SELECT * FROM myapp_project WHERE (ref, version) IN
(SELECT ref, max(version) FROM myapp_project GROUP BY ref)

Alternatively (slightly simpler perhaps):

SELECT * from myapp_project p
WHERE p.version =
(SELECT max(version) FROM myapp_project p1 WHERE p1.ref = p.ref)

How do I achieve the same query using Django's ORM?

Edit: I've got as far as this -

foo = Project.objects.values('ref').annotate(version=Max('version'))

This gives me something that looks right if I inspect it. As soon as I try to get the id out with:

foo.values('id')

It seems to discard the original result and gives back all the rows.

Edit more:

Worked around it for now with .extra():

maxids = """id in (SELECT id from myapp_project p WHERE p.version = 
(SELECT max(version) FROM myapp_project p1 WHERE p1.ref = p.ref))"""

Project.objects.all().extra(where=[maxids])

Answer 1

Using in

here is an example straight from the linked document

inner_qs = Blog.objects.filter(name__contains='Cheddar')
entries = Entry.objects.filter(blog__in=inner_qs)

The exact query that you are using isn't one that is supported by all databases that work with Django. For example while it works on postgresql it does not work on sqlite so you would have to change your approach to use the primary key instead of the unique_together key. Or use a join.

Update the second query can be handled more easily but I will refrain from posting that here since that would seem like plagarizing @anand's answer.