Django orm get latest for each group
PythonDjangoDjango QuerysetDjango OrmPython Problem Overview
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.
Python Solutions
Solution 1 - Python
If your DB is postgres which supports distinct()
on field you can try
Score.objects.order_by('student__username', '-date').distinct('student__username')
Solution 2 - Python
This should work on Django 1.2+ and MySQL:
Score.objects.annotate(
max_date=Max('student__score__date')
).filter(
date=F('max_date')
)
Solution 3 - Python
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: https://stackoverflow.com/questions/7020313/filtering-django-query-by-the-record-with-the-maximum-column-value
Solution 4 - Python
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