Django ORM: Filter Primary Model Based On Chronological Fields From Related Model

Let us presume that we have the following models: class Patient(models.Model) name = models.CharField() # other fields following class MedicalFile(model.Model) patient

Solution 1:

If you are interested in seeing the files:

MedicalFile.objects.annotate(
    maxdate=Max(
        'patient__files__date'
        )).filter(
            maxdate=F('date'),
            diagnosis="flu").select_related(
                'patient'
            )

If you want the patients:

Patient.objects.annotate(
    maxdate=Max(
        'files__date'
        )).filter(
            maxdate=F('files__date'),
            files__diagnosis="flu"))

Great thanks to Roba, collaborative asking/answering is my favourite type of SO user.