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

Question

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

Navigation

#1 by (4 votes)

2

I want to create a queryset that returns only the cars that had the last approved revision. It is known that there may be n cars, and each car has a history of revisions, so there may be no 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'm currently performing a non-performative 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])

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

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

python django django-1.8

asked by anonymous 26.02.2015 / 03:44

1 answer

Class structure in Asp.net MVC How to customize the current location marker (GPS) in Maps api v2 android?

score 4 · Accepted Answer

First, select the highest date using annotate :

from django.db.models import Max, F

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

Then select the revisions that have both date equal to the 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, OUTER JOIN ensures that all revisions are fetched, and their maximum date is saved in max_data , while INNER JOIN ensures that only cars with approved revisions are returned, as long as this revision has the largest date among those present (ie equal to max_data ).