Django orm为每个组获取最新信息

I am using django 1.6 with Mysql

我正在使用django 1.6和Mysql

I have this 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()

Now i want to get the latest score record for each student.
i have tried

现在我想获得每个学生的最新成绩记录。我有试过

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

i have also tried

我也试过

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

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

正如Django ORM所描述的那样——获得了组的最新记录，但这并没有帮助。

3 个解决方案

#1

This should work on Django 1.2+ and MySQL:

这应该适用于Django 1.2+和MySQL:

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

#2

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

如果您的DB是在字段中支持distinct()的postgres，您可以尝试

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

#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

如果您想要完整的分数记录，看起来您将不得不使用一个原始的SQL查询:以最大列值的记录过滤Django查询。

#1