Django: how to get the latest related object

Question

I'm working on optimization Django queries and struggling with getting the last related item.

Models:

class Car(models.Model):

    title = models.CharField(max_length=255)

class CarTechnicalInspection(models.Model):

    car = models.ForeignKey(Car)

    date_to = models.DateField()

    class Meta:

        ordering = ['date_to', ]

View:

cars = Car.objects.all()

for car in cars:

    ti = car.cartechnicalinspection_set.last() #hit DB query for each car iteration!

    if ti and ti.date_to < timezone.now():

         #do something

How to Annotate the last Technical Inspection for each car without a DB query for each car? Thanks!

P.S. prefetch_related('cartechnicalinspection_set') does not work

Answer 1

So you need all the cars that the date_to is before timezone.now() which is now and you need the the date_to for these cars.

from django.db.models import Max

# Filter: cars with date_to less than timezone.now()

# Annotate: attach the greatest date_to as date_to to each car object.

cars = Car.objects.filter(cartechnicalinspection__date_to__lt=timezone.now()).annotate(date_to=Max('cartechnicalinspection__date_to'))

Now if you print them like this:

for car in cars:

    print(car.date_to)

You'll get the date_to for each car that the date_to less than timezone.now()