将经过过滤的自联接字段的聚合添加到管理员list_display [英] Adding aggregate over filtered self-join field to Admin list_display

问题描述

我想为我的一位模型管理员增加一个有趣的价值。给定这样的模型：

I would like to augment one of my model admins with an interesting value. Given a model like this:

class Participant(models.Model):
    pass

class Registration(models.Model):
    participant = models.ForeignKey(Participant)
    is_going = models.BooleanField(verbose_name='Is going')

现在，我想显示该注册数量 >参与者，其中 is_going 是 False 。因此，类似于此SQL查询：

Now, I would like to show the number of other Registrations for this Participant where is_going is False. So, something akin to this SQL query:

SELECT reg.*, COUNT(past.id) AS not_going_num
FROM   registrations AS reg, registrations AS past
WHERE  past.participant_id = reg.participant_id AND
       past.is_going = False

我认为我可以根据以下方法扩展 Admin 的 queryset（）方法 Django Admin，通过注释显示来自相关模型的聚合值它带有额外的 Count ，但我仍然不知道如何处理自联接并过滤掉它。

I think I can extend the Admin's queryset() method according to Django Admin, Show Aggregate Values From Related Model, by annotating it with the extra Count, but I still cannot figure out how to work the self-join and filter into this.

我查看了通过django ORM和Django self join，如何将此查询转换为ORM查询， bu前者正在执行 SELECT * ，而后者似乎存在数据模型问题。

I looked at Self join with django ORM and Django self join , How to convert this query to ORM query, but the former is doing SELECT * AND the latter seems to have data model problems.

关于如何解决这个问题？

Any suggestions on how to solve this?

推荐答案

查看答案的先前版本的编辑历史记录。

See edit history for previous version of the answer.

对于每个注册模型，下面的管理实现将显示不进行计数。 不进行计数是注册的参与者的 is_going = False 的计数。

The admin implementation below will display "Not Going Count" for each Registration model. The "Not Going Count" is the count of is_going=False for the registration's participant.

@admin.register(Registration)
class RegistrationAdmin(admin.ModelAdmin):

    list_display = ['id', 'participant', 'is_going', 'ng_count']

    def ng_count(self, obj):
        return obj.not_going_count
    ng_count.short_description = 'Not Going Count'

    def get_queryset(self, request):
        qs = super(RegistrationAdmin, self).get_queryset(request)
        qs = qs.filter(participant__registration__isnull=False)
        qs = qs.annotate(not_going_count=Sum(
            Case(
                When(participant__registration__is_going=False, then=1),
                default=0,
                output_field=models.IntegerField())
            ))
        return qs

---

下面是对Qu的更详尽的解释erySet：

---

Below is a more thorough explanation of the QuerySet:

qs = qs.filter(participant__registration__isnull=False)

过滤器使Django执行两个联接-一个 INNER JOIN 到参与者表，一个向左外加入到注册表。

The filter causes Django to perform two joins - an INNER JOIN to participant table, and a LEFT OUTER JOIN to registration table.

qs = qs.annotate(not_going_count=Sum(
    Case(
        When(participant__registration__is_going=False, then=1),
        default=0,
        output_field=models.IntegerField())
    )    
))

这是一个标准总计，将用于 SUM 最多为 is_going = False 。这将转换为SQL

This is a standard aggregate, which will be used to SUM up the count of is_going=False. This translates into the SQL

SUM(CASE WHEN past."is_going" = False THEN 1 ELSE 0 END)

为每个注册模型生成总和，并且该总和属于注册的参与者。

The sum is generated for each registration model, and the sum belongs to the registration's participant.

这篇关于将经过过滤的自联接字段的聚合添加到管理员list_display的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！