Django ORM: is it possible to inject subqueries?

Question

I have a Django model that looks something like this:

class Result(models.Model):
    date = DateTimeField()
    subject = models.ForeignKey('myapp.Subject')
    test_type = models.ForeignKey('myapp.TestType')
    summary = models.PositiveSmallIntegerField()
    # more fields about the result like its location, tester ID and so on

Sometimes we want to retrieve all the test results, other times we only want the most recent result of a particular test type for each subject. This answer has some great options for SQL that will find the most recent result.

Also, we sometimes want to bucket the results into different chunks of time so that we can graph the number of results per day / week / month.

We also want to filter on various fields, and for elegance I'd like a QuerySet that I can then make all the filter() calls on, and annotate for the counts, rather than making raw SQL calls.

I have got this far:

qs = Result.objects.extra(select = {
         'date_range': "date_trunc('{0}', time)".format("day"), # Chunking into time buckets
         'rn' : "ROW_NUMBER() OVER(PARTITION BY subject_id, test_type_id ORDER BY time DESC)"})
qs = qs.values('date_range', 'result_summary', 'rn')
qs = qs.order_by('-date_range')

which results in the following SQL:

SELECT (ROW_NUMBER() OVER(PARTITION BY subject_id, test_type_id ORDER BY time DESC)) AS "rn", (date_trunc('day', time)) AS "date_range", "myapp_result"."result_summary" FROM "myapp_result" ORDER BY "date_range" DESC

which is kind of approaching what I'd like, but now I need to somehow filter to only get the rows where rn = 1. I tried using the 'where' field in extra(), which gives me the following SQL and error:

SELECT (ROW_NUMBER() OVER(PARTITION BY subject_id, test_type_id ORDER BY time DESC)) AS "rn", (date_trunc('day', time)) AS "date_range", "myapp_result"."result_summary" FROM "myapp_result" WHERE "rn"=1 ORDER BY "date_range" DESC                                                                                                                            ;
ERROR:  column "rn" does not exist

So I think the query that finds "rn" needs to be a subquery - but is it possible to do that somehow, perhaps using extra()?

I know I could do this with raw SQL but it just looks ugly! I'd love to find a nice neat way where I have a filterable QuerySet.

I guess the other option is to have a field in the model that indicates whether it is actually the most recent result of that test type for that subject...

Answer 1

I've found a way!

qs = Result.objects.extra(where = ["NOT EXISTS(SELECT * FROM myapp_result as T2 WHERE (T2.test_type_id = myapp_result.test_type_id AND T2.subject_id = myapp_result.subject ID AND T2.time > myapp_result.time))"])

This is based on a different option from the answer I referenced earlier. I can filter or annotate qs with whatever I want.

---

As an aside, on the way to this solution I tried this:

qq = Result.objects.extra(where = ["NOT EXISTS(SELECT * FROM myapp_result as T2 WHERE (T2.test_type_id = myapp_result.test_type_id AND T2.subject_id = myapp_result.subject ID AND T2.time > myapp_result.time))"])
qs = Result.objects.filter(id__in=qq)

Django embeds the subquery just as you want it to:

SELECT ...some fields... FROM "myapp_result" 
WHERE ("myapp_result"."id" IN (SELECT "myapp_result"."id" FROM "myapp_result" 
WHERE (NOT EXISTS(SELECT * FROM myapp_result as T2 
WHERE (T2.subject_id = myapp_result.subject_id AND T2.test_type_id = myapp_result.test_type_id AND T2.time > myapp_result.time))))) 

I realised this had more subqueries than I need, but I note it here as I can imagine it being useful to know that you can filter one queryset with another and Django does exactly what you'd hope for in terms of embedding the subquery (rather than, say, executing it and embedding the returned values, which would be horrid.)