Django orm get latest for each group

python django django-queryset django-orm

28,538

Solution 1

This should work on Django 1.2+ and MySQL:

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

Solution 2

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

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

Solution 3

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

28,538

Author by

yossi

“Place no head above your own.”

Updated on July 05, 2022

Comments

yossi almost 2 years

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.

yossi over 10 years

unfortunately i am using mysql
e18r about 9 years

this takes ages to run!
nitwit about 9 years

@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.
Tomas Tomecek almost 8 years

You may run into issues when trying to aggregate such query: NotImplementedError: aggregate() + distinct(fields) not implemented.
gabn88 over 7 years

This is reeaaaallly slow if you have a lot of Scores.
Dan almost 7 years

It's important to not call things like .update() on this queryset. As of 1.11, this will silently do the wrong thing.
Arman Ordookhani over 6 years

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.
NirIzr over 5 years

Will this also work if I prefix the order_by with a filter_by?
user2471801 almost 5 years

@NirIzr yes, put the filter before the order_by() and it should work as expected.
ox. almost 5 years

this is why i love postgresql
Eray Erdin over 4 years

I think it now works with all officially supported databases since the documentation does not say it's specific to PostgreSQL?
William over 3 years

Unfortunately as of Feb 2021, it's not supported by CloudSQL in GAE.