如何在Django REST Framework中统计每位学生的选课数量？

模型定义 #

首先修正模型中的拼写错误（models.charField改为models.CharField），同时补充CharField必填的max_length参数，完整模型代码如下：

from django.db import models

class Students(models.Model):
    id = models.AutoField(primary_key=True, unique=True)
    name = models.CharField(max_length=100)

class Enrollments(models.Model):
    enroll_id = models.AutoField(primary_key=True, unique=True)
    student_id = models.ForeignKey(Students, on_delete=models.CASCADE, related_name='enrollments')
    subjects = models.CharField(max_length=100)

需求说明 #

需要实现类似以下SQL的查询效果，统计每位学生的选课数量，必须包含选课数量为0的学生：

select
    s.id, s.name, count(e.subjects) as count
from Students as s
left outer join Enrollments as e
    on e.student_id_id = s.id
group by s.id, s.name
order by count asc;

实现方案 #

1. 编写查询逻辑 #

利用Django ORM的annotate和Count方法实现左外连接统计，自动处理0选课记录的情况：

from django.db.models import Count

# 查询所有学生并标注选课数量
students_with_enroll_count = Students.objects.annotate(
    count=Count('enrollments__subjects', default=0)
).order_by('count')

• Count('enrollments__subjects')：通过反向关联enrollments（由related_name指定）关联Enrollments表，统计每个学生的选课记录数
• default=0：确保没有选课记录的学生，count字段值为0
• order_by('count')：按选课数量升序排列结果

2. 编写序列化器 #

创建序列化器，将查询结果转为API响应格式：

from rest_framework import serializers

class StudentEnrollCountSerializer(serializers.ModelSerializer):
    count = serializers.IntegerField()  # 序列化新增的count字段

    class Meta:
        model = Students
        fields = ['id', 'name', 'count']

3. 编写API视图 #

实现返回统计结果的API视图：

from rest_framework.views import APIView
from rest_framework.response import Response

class StudentEnrollCountView(APIView):
    def get(self, request):
        queryset = Students.objects.annotate(
            count=Count('enrollments__subjects', default=0)
        ).order_by('count')
        serializer = StudentEnrollCountSerializer(queryset, many=True)
        return Response(serializer.data)

4. 配置URL路由 #

将视图映射到URL：

from django.urls import path
from .views import StudentEnrollCountView

urlpatterns = [
    path('student-enroll-count/', StudentEnrollCountView.as_view(), name='student-enroll-count'),
]

效果验证 #

请求该API后，返回的JSON数据将与需求中的示例结果一致：

[
    {"id": 2, "name": "b", "count": 0},
    {"id": 1, "name": "a", "count": 1},
    {"id": 3, "name": "c", "count": 2}
]

内容的提问来源于stack exchange，提问作者Sharath Kumar Gajavelli