Django - retrieve objects from latest date for each group - PersonPhoto

Question

My DB contains passport images of different people. Something like:

class Person(models.Model):
    pass

class PersonPhoto(models.Model):
    date_captured = models.DateField()
    person = models.ForeignKey(Person, null=False)

I want to extract for each person all of the images from the latest date he was photographed. So if person A has photos from August 5, 5, 9, 11, 11, and person B has images from August 7, 9, 13, 13, 19, 19 then I want to fetch both images from August 11 for person A, and both images from August 19 for person B.

The way I'm currently doing that is something like:

specific_dates_queryset = Q()
for photo in PersonPhoto.objects.all().values('person_id').annotate(max_date=Max('date_captured')):
    specific_dates_queryset |= Q(person_id=photo["person_id"], date_captured=photo["max_date"])


for photo in PersonPhoto.objects.filter(specific_dates_queryset).order_by("person_id"):
    print(f"image for person {photo.person_id}, of date {photo.date_captured}")

The idea is to first find the latest date of a photo for each person, and then in a new query to fetch these images for these people from these dates.

Is there a simpler solution that does everything within the DB and avoids redundant queries and data fetching?

Answer 1

A simple way to do this in a single query is to annotate each photo with the latest date for the related person and then filter by the annotation. This should return all desired PersonPhoto in a queryset

from django.db.models import Max, F

PersonPhoto.objects.annotate(
   latest=Max('person__personphoto__date_captured')
).filter(
    date_captured=F('latest')
)

I'm not sure how performant this will be due to annotation, this may depend on which DB you are using and the nature of your data

Answer 2

You can prefetch all the related photos of a person, and filter those based on the person's latest date captured like this:

from django.db.models import F, Max, Prefetch

person_qs = Person.objects.annotate(
    latest_photo_date=Max('personphoto__date_captured')
).prefetch_related(
    Prefetch(
        'personphoto_set',
        queryset=PersonPhoto.objects.annotate(
            person_latest_photo_captured=Max('person__personphoto__date_captured')
        ).filter(
            date_captured=F('person_latest_photo_captured')
        ),
        to_attr='latest_photos',
    )
)

All latest PersonPhoto instances will be available as a list in the latest_photos attribute of a Person instance, so you can access them like this:

for person in person_qs:
    print(f'Latest images for {person.name} taken on {person.latest_photo_date}:')
    for photo in person.latest_photos:
        print(f'Photo ID: {photo.id} - Captured at: {photo.date_captured}')
    print()

Output:

Latest images for B taken on 2021-08-19:
Photo ID: 10 - Captured at: 2021-08-19
Photo ID: 11 - Captured at: 2021-08-19

Latest images for A taken on 2021-08-11:
Photo ID: 5 - Captured at: 2021-08-11
Photo ID: 4 - Captured at: 2021-08-11

This will in total do two queries, one for the person list, and another for getting all the filtered related photos of each person.