sql - Django ORM - Get latest record for group

Question

Imagine we have the Django ORM model Meetup with the following definition:

class Meetup(models.Model):
    language = models.CharField()
    date = models.DateField(auto_now=True)

I'd like to fetch the latest meetup for each language.

It would seem you could use Django Aggregates to make this lookup easy:

Meetup.objects.annotate(latest_date=Max("date")).values("language", "latest_date")

In my mind this should fetch the "latest" meetup for each language. But that's not the case:

>>> Meetup.objects.create(language='python')
<Meetup: Meetup object>
>>> Meetup.objects.create(language='python')
<Meetup: Meetup object>
>>> Meetup.objects.create(language='node')
<Meetup: Meetup object>
>>> Meetup.objects.create(language='node')
<Meetup: Meetup object>
>>> Meetup.objects.annotate(latest_date=Max("date")).values("language", "latest_date").count()
4

I expected to get just the two latest Python and Node meetups!

How can I construct a query that will fetch only the latest meetups for each language?

PS. I'm using MySQL as my backend.

Answer 1

Put your values clause before the annotate.

From the aggregation docs:

If the values() clause precedes the annotate(), the annotation will be computed using the grouping described by the values() clause.

However, if the annotate() clause precedes the values() clause, the annotations will be generated over the entire query set. In this case, the values() clause only constrains the fields that are generated on output.

So this should do it:

Meetup.objects.values('language').annotate(latest_date=Max('date'))