Django orm get latest for each group

python django django-queryset django-orm
yossi picture yossi · Nov 12, 2013 · Viewed 22.4k times · Source

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.

Answer

Rohan picture Rohan · Nov 12, 2013

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

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

Related questions

How to perform OR condition in django queryset?

I want to write a Django query equivalent to this SQL query: SELECT * from user where income >= 5000 or income is NULL. How to construct the Django queryset filter? User.objects.filter(income__gte=5000, income=0) This doesn't work, because it …

Read More
Django ORM - objects.filter() vs. objects.all().filter() - which one is preferred?

Very often I see constructs like MyModel.objects.all().filter(...) which will return a QuerySet of the default Mananger. At first all() seems to be quite redundant, because MyMode.objects.filter(...) delivers the same result. However, this seems to be …

Read More
Django, query filtering from model method

I have these models: def Foo(Models.model): size = models.IntegerField() # other fields def is_active(self): if check_condition: return True else: return False def Bar(Models.model): foo = models.ForeignKey("Foo") # other fields Now I want to query …

Read More