How to filter a nested serializer's field in Django DRF

Question

I have two models named 'School' and 'Student'. I've created each's serializers and the nested serializer for School having a student serializer as a nested field.

Here I want to apply filters on the fields of the serializers using 'django-filters' and it is almost working, BUT ...the problem is that when I filter the nested field, i-e 'students's field' , It doesn't show me the required result. My models are :

class School(models.Model):
    name = models.CharField(max_length=256)
    principal = models.CharField(max_length=256)
    location = models.CharField(max_length=256)
    is_government = models.BooleanField(default=True)

    def __str__(self):
        return self.name


class Student(models.Model):
    name = models.CharField(max_length=256)
    age = models.PositiveIntegerField()
    school = models.ForeignKey(School,related_name='students',on_delete = models.CASCADE)
    is_adult  = models.BooleanField(default=True)

    def __str__(self):
        return self.name

and my serializers are:

class SchoolSerializer(serializers.ModelSerializer):

    def __init__(self, *args, **kwargs):
    # Don't pass the 'fields' arg up to the superclass
        # Instantiate the superclass normally
        super(SchoolSerializer, self).__init__(*args, **kwargs)
        allow_students = self.context.get("allow_students",None)
        if allow_students:
            self.fields['students'] = StudentSerializer(many=True, context=kwargs['context'], fields=['name','age','is_adult']) 


    class Meta():
        model = School
        fields = '__all__'


class StudentSerializer(DynamicFieldsModelSerializer):
    class Meta():
        model = Student
        fields = '__all__'

and these are the filters that i am using in my views:

from django_filters.rest_framework import DjangoFilterBackend
from django_filters import FilterSet
from django_filters import rest_framework as filters


class SchoolStudentAPIView(generics.ListAPIView, mixins.CreateModelMixin):
    queryset              = School.objects.all()
    serializer_class      = SchoolSerializer

    filter_backends       = (DjangoFilterBackend,)
    filter_fields         = ('is_government','students__is_adult')

Here, the issue is that when i search for "students__is_adult", which is a nested field, It filters out the list of students that are adult ALONG WITH THE students that are not.

Can someone add something extra or give another solutuion? thank you

Answer 1

The problem

First of all, Django Rest Framework is not doing the query you'd expect. Let's see how to check.

One way to debug the actual query is adding a custom list() method to the SchoolStudentAPIView class, as follows:

    def list(self, request, *args, **kwargs):
        resp = super().list(request, *args, **kwargs)
        from django.db import connection
        print(connection.queries) # or set a breakpoint here
        return resp

This method does nothing more than dumping all the executed queries to the console.

The last element of connection.queries is what we should focus on. It'll be a dict() with its "sql" key looking something like:

SELECT `school`.`id`, `school`.`name`, `school`.`location`, `school`.`is_government`
FROM `school` INNER JOIN `student` ON (`school`.`id` = `student`.`school_id`)
WHERE `student`.`is_adult` = 1

This query means that the SchoolSerializer will be passed all the Schools that have at least one adult Student.

By the way, the same school can appear multiple times, since the above query produces one row per adult student.

The SchoolSerializer, in the end, shows all the Students in the School regardless of any filtering option: this is what this line achieves.

        if allow_students:
            self.fields['students'] = StudentSerializer(many=True, ...) 

Suggested solution

No simple solution is to be found with serializers. Maybe the more straightforward way is to write a custom list() method in the SchoolStudentAPIView class.

The method will:

• look for the query string argument student__is_adult: if it's there, the method will create a custom field on each School in the queryset (I named it filtered_students), and make that field point to the correct Student queryset.
• pass a context argument to the SchoolSerializer, to tell it that students are filtered

The SchoolSerializer class, in turn, will populate its students field in two different ways, depending on the presence or absence of the context argument. Specifically, the StudentSerializer field will have the source kwarg if the students__is_adult key is present in the passed context.

In code:

class SchoolStudentAPIView(generics.ListAPIView, mixins.CreateModelMixin):
    # ...
    def list(self, request, *args, **kwargs):
        schools = self.get_queryset()
        ctx = {}
        if 'students__is_adult' in request.query_params:
            filter_by_adult = bool(request.query_params['students__is_adult'])
            ctx = {
                'students__is_adult': filter_by_adult,
                'allow_students': True,
            }
            for s in schools:
                s.filtered_students = s.students.filter(is_adult=filter_by_adult)

        ser = SchoolSerializer(data=schools, many=True, context=ctx)
        ser.is_valid()
        return Response(ser.data)

class SchoolSerializer(serializers.ModelSerializer):

    def __init__(self, *args, **kwargs):
        super(SchoolSerializer, self).__init__(*args, **kwargs)
        allow_students = self.context.get("allow_students", None)
        if allow_students:
            # Change 'source' to custom field if students are filtered
            filter_active = self.context.get("posts__is_active", None)
            if filter_active is not None:
                stud = StudentSerializer(
                    source='filtered_students', many=True,
                    context=kwargs['context'],
                    fields=['name', 'age', 'is_adult'])
            else:
                stud = StudentSerializer(
                    many=True, context=kwargs['context'],
                    fields=['name', 'age', 'is_adult'])

            self.fields['students'] = stud