有如下模型关系

models.py

class Student(models.Model):
    name = models.CharField('学生名', max_length=20)
    is_del = models.IntegerField(default=0)

    def __str__(self):
        return self.name


class Grade(models.Model):
    name = models.CharField('年级名', max_length=20)
    student = models.ManyToManyField(Student)
    is_del = models.IntegerField(default=0)

    def __str__(self):
        return self.name


class School(models.Model):
    name = models.CharField('学校名', max_length=20)
    grade = models.ManyToManyField(Grade)
    is_del = models.IntegerField(default=0)

    def __str__(self):
        return self.name

序列化School的时候可以需要把Grade和Student带上。

serializer.py

class StudentSerializer(ModelSerializer):
    class Meta:
        model = Student
        fields = ('name', 'is_del')


class GradeSerializer(ModelSerializer):
    student = StudentSerializer(many=True)

    class Meta:
        model = Grade
        fields = ('name', 'is_del', 'student')


class SchoolSerializer(ModelSerializer):
    grade = GradeSerializer(many=True)

    class Meta:
        model = School
        fields = ('name', 'is_del', 'grade')

此时数据库查询

[0.000] SELECT "link_school"."id", "link_school"."name", "link_school"."is_del" FROM "link_school"
[0.000] SELECT "link_grade"."id", "link_grade"."name", "link_grade"."is_del" FROM "link_grade" INNER JOIN "link_school_grade" ON ("link_grade"."id" = "link_school_grade"."grade_id") WHERE "link_school_grade"."school_id" = 1
[0.000] SELECT "link_student"."id", "link_student"."name", "link_student"."is_del" FROM "link_student" INNER JOIN "link_grade_student" ON ("link_student"."id" = "link_grade_student"."student_id") WHERE "link_grade_student"."grade_id" = 1
[0.000] SELECT "link_student"."id", "link_student"."name", "link_student"."is_del" FROM "link_student" INNER JOIN "link_grade_student" ON ("link_student"."id" = "link_grade_student"."student_id") WHERE "link_grade_student"."grade_id" = 2
[0.000] SELECT "link_student"."id", "link_student"."name", "link_student"."is_del" FROM "link_student" INNER JOIN "link_grade_student" ON ("link_student"."id" = "link_grade_student"."student_id") WHERE "link_grade_student"."grade_id" = 3

使用Prefetch_related

view.py

class SchoolListView(ListAPIView):
    serializer_class = SchoolSerializer
    queryset = School.objects.all().prefetch_related('grade', 'grade__student')

此时数据库查询只有三条，数量多时效果会比较明显

[0.001] SELECT "link_school"."id", "link_school"."name", "link_school"."is_del" FROM "link_school"
[0.000] SELECT ("link_school_grade"."school_id") AS "_prefetch_related_val_school_id", "link_grade"."id", "link_grade"."name", "link_grade"."is_del" FROM "link_grade" INNER JOIN "link_school_grade" ON ("link_grade"."id" = "link_school_grade"."grade_id") WHERE "link_school_grade"."school_id" IN (1)
[0.000] SELECT ("link_grade_student"."grade_id") AS "_prefetch_related_val_grade_id", "link_student"."id", "link_student"."name", "link_student"."is_del" FROM "link_student" INNER JOIN "link_grade_student" ON ("link_student"."id" = "link_grade_student"."student_id") WHERE "link_grade_student"."grade_id" IN (1, 2, 3)

prefetch_related 使用一条SQL把所有属于 school 的 grade 和所有属于 grade 的 student 查出来,用python做关联。
grade 关联的所有 student 存在如下变量中

cache.png

过滤数据

当需要过滤不需要的数据时，可以自定义DRF中的 ListSerializer，重写它的 to_representation方法, 并在serializer中指定 list_serializer_class

class FilterdListSerializer(ListSerializer):
    def to_representation(self, data):
        data = data.filter(is_del=0)   # 过滤已删除的
        return super().to_representation(data)


class StudentSerializer(ModelSerializer):
    class Meta:
        model = Student
        fields = ('name', 'is_del')
        list_serializer_class = FilterdListSerializer


class GradeSerializer(ModelSerializer):
    student = StudentSerializer(many=True)

    class Meta:
        model = Grade
        fields = ('name', 'is_del', 'student')
        list_serializer_class = FilterdListSerializer


class SchoolSerializer(ModelSerializer):
    grade = GradeSerializer(many=True)

    class Meta:
        model = School
        fields = ('name', 'is_del', 'grade')

DRF在meta中获取list_serializer_class,默认使用 ListSerializer

meta = getattr(cls, 'Meta', None)
list_serializer_class = getattr(meta, 'list_serializer_class', ListSerializer)
return list_serializer_class(*args, **list_kwargs)

此时数据库查询

[0.000] SELECT "link_school"."id", "link_school"."name", "link_school"."is_del" FROM "link_school"
[0.001] SELECT ("link_school_grade"."school_id") AS "_prefetch_related_val_school_id", "link_grade"."id", "link_grade"."name", "link_grade"."is_del" FROM "link_grade" INNER JOIN "link_school_grade" ON ("link_grade"."id" = "link_school_grade"."grade_id") WHERE "link_school_grade"."school_id" IN (1)
[0.001] SELECT ("link_grade_student"."grade_id") AS "_prefetch_related_val_grade_id", "link_student"."id", "link_student"."name", "link_student"."is_del" FROM "link_student" INNER JOIN "link_grade_student" ON ("link_student"."id" = "link_grade_student"."student_id") WHERE "link_grade_student"."grade_id" IN (1, 2, 3)
[0.000] SELECT "link_grade"."id", "link_grade"."name", "link_grade"."is_del" FROM "link_grade" INNER JOIN "link_school_grade" ON ("link_grade"."id" = "link_school_grade"."grade_id") WHERE ("link_school_grade"."school_id" = 1 AND "link_grade"."is_del" = 0)
[0.000] SELECT "link_student"."id", "link_student"."name", "link_student"."is_del" FROM "link_student" INNER JOIN "link_grade_student" ON ("link_student"."id" = "link_grade_student"."student_id") WHERE ("link_grade_student"."grade_id" = 2 AND "link_student"."is_del" = 0)
[0.001] SELECT "link_student"."id", "link_student"."name", "link_student"."is_del" FROM "link_student" INNER JOIN "link_grade_student" ON ("link_student"."id" = "link_grade_student"."student_id") WHERE ("link_grade_student"."grade_id" = 3 AND "link_student"."is_del" = 0)

又变多了，多出来的最后三条可以看到，分别查了 school_id=1 的 grade, grade=2和3 时的 student

Django文档上说明了原因:

Remember that, as always with QuerySets, any subsequent chained methods which imply a different database query will ignore previously cached results, and retrieve data using a fresh database query.

prefetch_related后再跟filter时会重新去查数据库，所以在序列化 school_id为1的 grade 时查了一次数据库， 序列化 grade_id 为2 和3的 student 时查了两次数据库

**** 解决方法 ****

使用Prefetch对象指定queryset，此时serializer.py中便不需要自定义ListSerializer了

views.py

class SchoolListView(ListAPIView):
    serializer_class = SchoolSerializer
    queryset = School.objects.all().prefetch_related(
        Prefetch('grade', queryset=Grade.objects.filter(is_del=0).prefetch_related(
            Prefetch('student', queryset=Student.objects.filter(is_del=0))
        )),
    )

也可以这么写

    queryset = School.objects.all().prefetch_related(
        Prefetch('grade', queryset=Grade.objects.filter(is_del=0)),
        Prefetch('grade__student', queryset=Student.objects.filter(is_del=0))
    )

过滤后的结果

json.png