How to build a queryset that returns only the cars with the last approved revision?

Question

I’m looking to create a queryset that returns only the cars that have had the last approved review. It is known that there may be n cars, and each car has a history of revisions, so there may be n revisions for each car.

class Carro(models.Model):
    marca = models.CharField(max_length=100)

class Revisao(models.Model):
    carro = models.ForeignKey(Carro)
    data = models.DateField()
    aprovado = models.BooleanField(default=False)

I am currently performing a not very performatic algorithm, with the following logic:

lista_de_aprovados = []
for carro in Carro.objects.filter(revisao=True):
    try:
        carro_aprovado = carro.revisao_set.filter(aprovado=True).latest('data')
    except ObjectsDoesNotExist:
        pass
    else:
        lista_de_aprovados.append(carro_aprovado)
carros_aprovados = Carros.objects.filter(id__in=[l.pk for l in lista_de_aprovados])

Is there a way to accomplish this with just a queryset? I’m using Django 1.8 and the problem is that I’m realizing that same logic more than once and it’s slowing down a bit when it comes to loading the page. As I said earlier, the idea is a queryset that returns only the cars that had the last approved revision.

Carros.objects.filter(revisao=True).filter(...logica)

Answer 1

First, select the biggest date using annotate:

from django.db.models import Max, F

Carro.objects.annotate(max_data=Max('revisao__data'))...

Then select the revisions that both have date equal to maximum and are approved:

.filter(revisao__data=F('max_data'), revisao__aprovado=True)

The result will be a query like this:

SELECT "app_carro"."id", "app_carro"."marca", MAX("app_revisao"."data") AS "max_data"
FROM "app_carro" 
    LEFT OUTER JOIN "app_revisao" ON ( "app_carro"."id" = "app_revisao"."carro_id" )
    INNER JOIN "app_revisao" T3 ON ( "app_carro"."id" = T3."carro_id" )
WHERE T3."aprovado" = True
GROUP BY "app_carro"."id", "app_carro"."marca", T3."data"
HAVING T3."data" = MAX("app_revisao"."data");

That is, the OUTER JOIN ensures that all revisions are searched, and the maximum date of the revisions is saved in max_data, as long as the INNER JOIN ensures that only cars with approved revisions are returned, provided that this revision has the longest date among those present (i.e. equal to max_data).