Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django orm get latest for each group

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.

like image 432
yossi Avatar asked Nov 12 '13 08:11

yossi


2 Answers

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

Score.objects.order_by('student__username', '-date').distinct('student__username')
like image 79
Rohan Avatar answered Nov 10 '22 14:11

Rohan


This should work on Django 1.2+ and MySQL:

Score.objects.annotate(
  max_date=Max('student__score__date')
).filter(
  date=F('max_date')
)
like image 43
nitwit Avatar answered Nov 10 '22 13:11

nitwit