在Django中查询Organization关联申诉统计及日期过滤实现方案

一、模型定义 #

class Organization(models.Model):
    name = models.CharField(max_length=250, unique=True)

    def __str__(self):
        return self.name 

class AppealForm(models.Model):
    form_name = models.CharField(max_length=100)

    def __str__(self):
        return self.report_form_name  # 注：此处疑似笔误，应为self.form_name

class Appeal(models.Model):
    organization = models.ForeignKey(Organization, on_delete=models.SET_NULL, blank=True, null=True)
    appeal_form = models.ForeignKey(AppealForm, on_delete=models.CASCADE, blank=True, null=True)
    appeal_number = models.CharField(max_length=100, blank=True, null=True)
    applicant_first_name = models.CharField(max_length=100, blank=True, null=True)
    applicant_second_name = models.CharField(max_length=100, blank=True, null=True)
    date = models.DateField(blank=True, null=True)
    appeal_body = models.TextField()

二、实例数据 #

Organization 数据 #

AppealForm 数据 #

Appeal 数据 #

三、需求统计表格 #

需要生成如下统计结果：

四、补充更新：过滤配置（filters.py） #

class AppealFilter(django_filters.FilterSet):
    appeal__date = django_filters.DateFromToRangeFilter(widget=django_filters.widgets.RangeWidget(attrs={'type': 'date'}))
    class Meta:
        model = Organization
        fields = ['appeal__date']  # 注：原代码中字段末尾多了空格，已修正

五、查询实现方案 #

基础统计查询 #

使用Django ORM的annotate结合Count、Case和When实现统计，确保无申诉的机构返回0值：

from django.db.models import Count, Case, When, IntegerField

organizations_with_stats = Organization.objects.annotate(
    # 统计申诉总数
    appeal_total=Count('appeal', distinct=False),
    # 统计书面申诉数量
    written_appeals=Count(
        Case(
            When(appeal__appeal_form__form_name='Written', then=1),
            output_field=IntegerField(),
        )
    ),
    # 统计口头申诉数量
    oral_appeals=Count(
        Case(
            When(appeal__appeal_form__form_name='Oral', then=1),
            output_field=IntegerField(),
        )
    )
).order_by('id')

结合日期过滤的查询 #

通过AppealFilter进行日期范围过滤后，再添加统计注解：

from .filters import AppealFilter

def get_filtered_organizations(request):
    queryset = Organization.objects.all()
    filterset = AppealFilter(request.GET, queryset=queryset)
    filtered_with_stats = filterset.qs.annotate(
        appeal_total=Count('appeal', distinct=False),
        written_appeals=Count(
            Case(
                When(appeal__appeal_form__form_name='Written', then=1),
                output_field=IntegerField(),
            )
        ),
        oral_appeals=Count(
            Case(
                When(appeal__appeal_form__form_name='Oral', then=1),
                output_field=IntegerField(),
            )
        )
    ).order_by('id')
    return filtered_with_stats

结果说明 #

• Count('appeal')自动统计机构关联的申诉记录数，无申诉的机构返回0
• Case/When实现条件计数，仅统计符合特定申诉类型的记录
• 日期过滤会自动作用于关联的Appeal记录，过滤后仍能正确生成统计数据

内容的提问来源于stack exchange，提问作者Firdavsbek Narzullaev