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.

Meetup.objects.filter(language="python").order_by('-date')[0] — Victor Castillo Torres, Jul 26 '13 at 18:07
@VictorCastilloTorres sorry it wasn't clear - the goal is to get all the unique meetup in a single query. — jb., Jul 26 '13 at 18:18

score 18 · Accepted Answer · edited Jun 20 '20 at 09:12

18

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'))

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 26 '13 at 17:17

Peter DeGlopper

36,326
7
90
83

>>> Meetup.objects.values("language").annotate(latest_date=Max("date")) [{'latest_date': datetime.date(2013, 7, 26), 'language': u'node'}, {'latest_date': datetime.date(2013, 7, 26), 'language': u'python'}] – jb. Jul 26 '13 at 18:10
Good answer but i can not find other fields in query result. In this example just two fields but if exist some other fields can not find them. – reza_khalafi Oct 29 '22 at 12:00
I do not have current object when set the result in for loop. – reza_khalafi Oct 29 '22 at 12:20

Django ORM - Get latest record for group

1 Answers1

Linked