Django orm get latest for each group

Question

I am using Django 1.6 with Mysql.

I have these models:

class Student(models.Model):
     username = models.CharField(max_length=200, unique = True)

class Score(models.Model)
     student = models.ForeignKey(Student)
     date = models.DateTimeField()
     score = models.IntegerField()

I want to get the latest score record for each student.
I have tried:

Score.objects.values('student').annotate(latest_date=Max('date'))

and:

Score.objects.values('student__username').annotate(latest_date=Max('date'))

as described Django ORM - Get the latest record for the group but it did not help.

score 78 · Answer 1 · answered Nov 12 '13 at 08:21

78

If your DB is postgres which supports distinct() on field you can try

Score.objects.order_by('student__username', '-date').distinct('student__username')

answered Nov 12 '13 at 08:21

Rohan

50,238
11
84
85

4

unfortunately i am using mysql – yossi Nov 12 '13 at 08:24
4

You may run into issues when trying to aggregate such query: `NotImplementedError: aggregate() + distinct(fields) not implemented.` – Tomas Tomecek Aug 15 '16 at 11:49
1

It's important to not call things like `.update()` on this queryset. As of 1.11, this will silently do the wrong thing. – Dan Sep 19 '17 at 11:05
1

Will this also work if I prefix the `order_by` with a `filter_by`? – NirIzr Oct 21 '18 at 03:38
@NirIzr yes, put the filter before the `order_by()` and it should work as expected. – monkut Aug 19 '19 at 05:55
this is why i love postgresql – ox. Aug 27 '19 at 22:08
I think it now works with all officially supported databases since the documentation does not say it's specific to PostgreSQL? – Eray Erdin Feb 21 '20 at 11:27
Unfortunately as of Feb 2021, it's not supported by CloudSQL in GAE. – William Feb 19 '21 at 00:38

score 41 · Accepted Answer · edited Feb 05 '19 at 19:51

41

This should work on Django 1.2+ and MySQL:

Score.objects.annotate(
  max_date=Max('student__score__date')
).filter(
  date=F('max_date')
)

edited Feb 05 '19 at 19:51

nael

1,371
15
36

answered Nov 12 '13 at 13:43

nitwit

1,675
2
16
20

3

this takes ages to run! – e18r May 28 '15 at 22:50
2

@emisilva I don't believe there is a more efficient way with this data structure and database backend. If you need it to be faster - either add an index or refactor the data structure. – nitwit May 29 '15 at 23:48
This is reeaaaallly slow if you have a lot of Scores. – gabn88 Dec 15 '16 at 23:30
4

Just for the record: This solution generates N^2 rows and filter them (N=Score count) that is not useful if N is larger than some thousands. Better to do one O(N) query and find `max_date` of each student and then issue another query do get actual `Score` objects. or use @Rohan's answer if you are on Postgres. – Arman Ordookhani Dec 12 '17 at 11:56

score 7 · Answer 3 · edited May 23 '17 at 12:18

7

I believe this would give you the student and the data

Score.objects.values('student').annotate(latest_date=Max('date'))

If you want the full Score records, it seems you will have to use a raw SQL query: Filtering Django Query by the Record with the Maximum Column Value

edited May 23 '17 at 12:18

Community

1
1

answered Nov 12 '13 at 08:19

Paul Draper

71,663
43
186
262

score -1 · Answer 4 · answered Oct 23 '20 at 21:56

Here's an example using Greatest with a secondary annotate. I was facing and issue where annotate was returning duplicate records ( Examples ), but the last_message_time Greatest annotation was causing duplicates.

qs = (
            Example.objects.filter(
                Q(xyz=xyz)
            )
            .exclude(
                 Q(zzz=zzz)
            )
            # this annotation causes duplicate Examples in the qs
            # and distinct doesn't work, as expected
            # .distinct('id') 
            .annotate(
                last_message_time=Greatest(
                    "comments__created",
                    "files__owner_files__created",
                )
            )
            # so this second annotation selects the Max value of the various Greatest
            .annotate(
                last_message_time=Max(
                    "last_message_time"
                )
            )
            .order_by("-last_message_time")
    )

reference:

https://docs.djangoproject.com/en/3.1/ref/models/database-functions/#greatest
from django.db.models import Max

Django orm get latest for each group

4 Answers4

Linked

Related