Django - "Find latest" of children for all rows

Question

I'm having difficulties with 'aggregate' and 'latest' .

I've got these two models :

class Word(models.Model):
    ESSENTIALWORDS = 'EW'
    FOOB = 'ER'
    OTHER = 'OT'
    WORDSOURCE_TYPE_CHOICES = (
        (ESSENTIALWORDS, 'Essential Words'),
        (FOOB, 'FOOB'),
        (OTHER, 'OTHER'),
    )

    level = models.IntegerField()
    word = models.CharField(max_length=30)
    source = models.CharField(max_length=2,
                              choices=WORDSOURCE_TYPE_CHOICES,
                              default=OTHER)
    hint = models.CharField(max_length=30, null=True, blank=True)


class Attempt(models.Model):
    learner = models.ForeignKey(Learner)
    word = models.ForeignKey(Word)
    when = models.DateTimeField(auto_now_add=True)
    success = models.BooleanField(default=False)

I want to find all Word objects for which the most recent Attempt (based on the Field when) has a success value of True.

Answer 1

This should work:

from django.db.models import Max, F

Word.objects.annotate(latest=Max('attempt__when'))
            .filter(attempt__success=True,
                    attempt__when=F('latest'))

First, every Word is annotated with the date of the most recent (i.e. the Max) attempt. Then the Word objects are filtered to only include ones that have a matching Attempt where success is True and where the date matches the latest date. (F is used to represent the value of another column—or in this case, an annotation.)