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

问题描述

我有一个名为student的数据库表，其中有一列名为marks。我想要数学中最高分的学生记录。有一个简单的解决方案，它使用 order_by（）[0] ：

  Student.objects.filter（subject ='Maths'）。order_by（' -  marks'）[0] 
  

但是这样排列表，然后提取第一条记录。如果我的桌子很大，这是多余的，因为我只需要最大记录。有没有办法得到最大的价值没有排序？

我想要整个对象，而不只是最大值。

感谢Anuj

解决方案

需要的SQL将是这样的：

  SELECT * 
 FROM STUDENT 
 WHERE marks =（SELECT MAX（marks）FROM STUDENT）
  

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

  max_marks = Student.objects.filter 
 subject ='Maths'
）.aggregate（maxmarks = Max（'marks'））['maxmarks'] 
 Student.objects.filter（subject ='Maths'，marks = max_marks ）
  

不幸的是，这个查询实际上是两个查询。最大标记聚合被执行，结果被拉入python，然后传递给第二个查询。有一个（令人惊讶的）没有办法传递一个只是一个聚合而不分组的查询，尽管它应该是可行的。我要打开一张票，看看可能如何修复。

编辑：

em>可以使用单个查询来执行此操作，但不是很明显。我没有在其他地方看到这种方法。

 从django.db.models导入值
 agg_qs =（
 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中打印此查询，您将获得：

  SELECT 
scratch_student。id，
scratch_student。name，
scratch_student。subject，
 scratch_student。mark
 FROMscratch_student
 WHERE（
scratch_student。subject= Maths 
 ANDscratch_student。marks=（
 SELECT 
 MAX（U0。marks）ASmax_marks
 FROMscratch_studentU0 
 WHERE U0。subject= Maths）） 
  

在Django 1.11（目前为alpha）中测试。这通过将注释分组到常数1（每行将分组）的工作。然后，我们从选择列表（第二个 values（）中删除​​该分组列。Django（现在）知道分组是多余的，并消除它。单个查询与我们需要的确切的SQL。

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.

Thanks Anuj

解决方案

The SQL required would be something like this:

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

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)

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.

Edit:

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
agg_qs = (
    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)

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))

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屋！