带有嵌套过滤器的Django注释 [英] Django annotation with nested filter

问题描述

是否可以在注释中过滤？

Is it possible to filter within an annotation?

在我看来，这样（这实际上不起作用）

In my mind something like this (which doesn't actually work)

Student.objects.all（）。注释（Count（'attendance'）。filter（type =Excused））

表格将让每个学生都有免费的缺勤人数。查看文档过滤器只能在注释之前或之后才能产生所需的结果。

The resultant table would have every student with the number of excused absences. Looking through documentation filters can only be before or after the annotation which would not yield the desired results.

解决方法是这个

for student in Student.objects.all():
    student.num_excused_absence = Attendance.objects.filter(student=student, type="Excused").count()

这是有效的，但是在许多查询中，在一个真实的应用程序中，这可能变得不切实际。我认为这种类型的语句在SQL中是可行的，但是如果可能的话，更愿意继续使用ORM。我甚至试图做出两个单独的查询（一个用于所有学生，另一个用于获取总数），并将它们与|组合。组合更改了总数：（

This works but does many queries, in a real application this can get impractically long. I think this type of statement is possible in SQL but would prefer to stay with ORM if possible. I even tried making two separate queries (one for all students, another to get the total) and combined them with |. The combination changed the total :(

我使用额外的sql 此处。

I solved the attendance problem using extra sql here.

• Timmy的博客文章很有用答案是基于它的。


• hash1baby的答案有效，但似乎同样复杂的sql。它还需要执行sql，然后将结果添加到for循环，这对我来说是坏的，因为我'我的解决方案构建了一个大量的查询器，具有大量的过滤器和附加功能，并且一次性执行。


• 如果性能没有问题 - 我建议循环工作，这是迄今为止最容易理解的。

• Timmy's blog post was useful. My answer is based off of it.
• hash1baby's answer works but seems equally complex as sql. It also requires executing sql then adding the result in a for loop. This is bad for me because I'm stacking lots of these filtering queries together. My solution builds up a big queryset with lots of filters and extra and executes it all at once.
• If performance is no issue - I suggest the for loop work around. It's by far the easiest to understand.

推荐答案

jango 1.8你可以直接在ORM中执行此操作：

As of Django 1.8 you can do this directly in the ORM:

students = Student.objects.all().annotate(num_excused_absences=models.Sum(
    models.Case(
        models.When(absence__type='Excused', then=1),
    default=0,
    output_field=models.IntegerField()
)))

从同一主题的另一个SO问题

我没有测试上面的样本，但在我自己的应用程序中完成了类似的操作。

I haven't tested the sample above but did accomplish something similar in my own app.

这篇关于带有嵌套过滤器的Django注释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！