Django:记录最大元素 [英] Django: Record with max element

问题描述

我有一个名为student"的数据库表，其中有一列名为marks".我想要数学最高分的学生记录.使用 order_by()[0] 有一个简单的解决方案:

I have a database table named 'student' in which there is one column named 'marks'. I want the student record with highest marks in Maths. There is a simple solution to it using order_by()[0]:

Student.objects.filter(subject='Maths').order_by('-marks')[0]

但这会对表格进行排序，然后获取我的第一条记录.如果我的表很大，这是多余的，因为我只需要最大记录.有没有办法不排序就得到最大值?

But this sorts the table and then fetches me the first record. If my table is huge, this is redundant as I need only the max record. Is there any way to just get the largest value without sorting?

我想要整个对象，而不仅仅是最大值.

I want the whole object, not just the max value.

谢谢阿努吉

推荐答案

所需的 SQL 如下:

The SQL required would be something like this:

SELECT *
FROM STUDENT
WHERE marks = (SELECT MAX(marks) FROM STUDENT)

要通过 Django 执行此操作，您可以使用 聚合 API.

To do this via Django, you can use the aggregation API.

max_marks = Student.objects.filter(
    subject='Maths'
).aggregate(maxmarks=Max('marks'))['maxmarks']
Student.objects.filter(subject='Maths', marks=max_marks)

不幸的是，这个查询实际上是两个查询.执行最大标记聚合，将结果拉入python，然后传递给第二个查询.没有办法(令人惊讶地)传递一个查询集，它只是一个没有分组的聚合，即使应该可以这样做.我要开一张票，看看如何解决这个问题.

Unfortunately, this query is actually two queries. The max mark aggregation is executed, the result pulled into python, then passed to the second query. There's (surprisingly) no way to pass a queryset that's just an aggregation without a grouping, even though it should be possible to do. I'm going to open a ticket to see how that might be fixed.

可以通过单个查询来做到这一点，但这不是很明显.我在别处没见过这种方法.

It is possible to do this with a single query, but it's not very obvious. I haven't seen this method elsewhere.

from django.db.models import Value

max_marks = (
    Student.objects
           .filter(subject='Maths')
           .annotate(common=Value(1))
           .values('common')
           .annotate(max_marks=Max('marks'))
           .values('max_marks')
)

Student.objects.filter(subject='Maths', marks=max_marks)

如果你在 shell 中打印这个查询，你会得到:

If you print this query in the shell you get:

SELECT 
       "scratch_student"."id", 
       "scratch_student"."name", 
       "scratch_student"."subject", 
       "scratch_student"."marks" 
  FROM "scratch_student" 
 WHERE ( 
       "scratch_student"."subject" = Maths 
   AND "scratch_student"."marks" = (
       SELECT 
              MAX(U0."marks") AS "max_marks" 
         FROM "scratch_student" U0 
        WHERE U0."subject" = Maths))

在 Django 1.11 上测试(目前处于 alpha 阶段).这是通过将注释按常量 1 分组来工作的，每一行都将分组到其中.然后我们从选择列表中删除这个分组列(第二个 values().Django(现在)知道足够的信息来确定分组是多余的，并消除它.留下一个带有确切 SQL 的查询我们需要.

Tested on Django 1.11 (currently in alpha). This works by grouping the annotation by the constant 1, which every row will group into. We then strip this grouping column from the select list (the second values(). Django (now) knows enough to determine that the grouping is redundant, and eliminates it. Leaving a single query with the exact SQL we needed.

这篇关于Django:记录最大元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！