Django-filter: Cannot Filter Reverse Foreign Key

[loganknecht]

Introduction

Hello! Thank you so much for this library! It has been such a pleasure to test it with my current API!

Current Goal

I have two models, a School and a SchoolCourse. SchoolCourse has a reverse many to one relationship to School.

I want to be able to filter the School object based on the name of the SchoolCourse

Additionally as a stretch goal, I would love to be able to filter by SchoolLesson eventually as well.

Data Model

School Model

class SchoolModel(Model):
    name = CharField(max_length=24, unique=True)

    REQUIRED_FIELDS = ["name"]

    class Meta:
        ordering = ("id",)

    def get_courses(self):
        school_courses = SchoolCourseModel.objects.filter(school=self)
        return school_courses

School Course

class SchoolCourseModel(Model):
    description = CharField(default="", max_length=200)
    name = CharField(max_length=50)
    school = ForeignKey(SchoolModel,
                        related_name="school_course",
                        on_delete=CASCADE)

    REQUIRED_FIELDS = ["school", "name"]

    class Meta:
        ordering = ("id",)
        unique_together = ("school", "name",)

    def get_lessons(self):
        school_lessons = SchoolLessonModel.objects.filter(course=self)
        return school_lessons

Which - if you play with these models in the debug view you'll see this

all_school_models[50].school_course
(Pdb++) <django.db.models.fields.related_descriptors.create_reverse_many_to_one_manager.<locals>.RelatedManager object at 0x10a8177d0>
all_school_models[50].school_course.all()
(Pdb++) <QuerySet [<SchoolCourseModel: SchoolCourseModel object (151)>, <SchoolCourseModel: SchoolCourseModel object (152)>, <SchoolCourseModel: SchoolCourseModel object (153)>]>
all_school_models[50].school_course.all()[0]
(Pdb++) <SchoolCourseModel: SchoolCourseModel object (151)>
all_school_models[50].school_course.all()[0].name
(Pdb++) 'Nihongo Course One'

Filter Configuration

The filter I made seems fairly straight-forward.

class SchoolFilter(FilterSet):
    school_name = CharFilter(field_name="name",
                             lookup_expr="icontains")
    # ---
    course_name = CharFilter(field_name="school_course__name",
                             lookup_expr="icontains")
    course_description = CharFilter(field_name="school_course__description",
                                    lookup_expr="icontains")

    class Meta:
        model = SchoolModel
        fields = [
            "school_name",
            # ---
            "course_name",
            "course_description"
        ]

Problem

However this does not work. If I make this request:
{{API_URL}}/api/v1/schools/?course_name=Nihongo

I get this back as a response

{
    "schools": []
}

Questions

What am I doing wrong here?

Am I misinterpreting django-filters? Am I misinterpreting how to connect the data models?

Any guidance is appreciated!

[rpkilby]

Hi @loganknecht. Just as a sanity check, can you verify that your URL is correct? In your example URL, the query string starts with a / instead of a ?.

[loganknecht]

Ah @rpkilby - apologies, this was an oversight on my part from copying and pasting from Postman. The url is actually {{API_URL}}/api/v1/schools/?offset=49&course_name=Nihongo but I was removing the offset to be less confusing and succeeded in doing the opposite 😂

[rpkilby]

Your example doesn't raise any obvious issues to me. My best guess is that there is some issue with the API view. Have you added the DjangoFilterBackend to your settings/view's filter_backends? And have you set filterset_class = SchoolFilter (note that this used to be filter_class)?

[loganknecht]

Hey @rpkilby

Here is the view code I have

class SchoolViewSet(ViewSet):
    http_method_names = ["get", "post"]

    def list(self, request):
        all_school_models = SchoolModel.objects.all()

        filtered_school_models = SchoolFilter(request.GET, queryset=all_school_models)
        # import pdb
        # pdb.set_trace()

        paginator = HeaderLinkPagination()
        current_page_results = paginator.paginate_queryset(filtered_school_models.qs,
                                                           request)

        all_schools_serializer = SchoolSerializer(current_page_results,
                                                  many=True)
        response_data = {
            "schools": all_schools_serializer.data
        }

        response_to_return = paginator.get_paginated_response(response_data)
        return response_to_return

The pdb.set_trace() is where I posted the above interrogation.

I did not sett the DjangoFilterBackend as I assumed that was a global configuration and I'm only testing this on a single endpoint.

Additionally I did not set this for the class either for the same reason.

It is important to know that I CAN use this filter on the name of the SchoolModel but when I filter on the reverse foreign key school_course it does not work.

[rpkilby]

Gotcha - so you're creating the filterset in the view directly. In that case, yeah, you wouldn't need to set the filter backend/filterset class.

You might check to see if the data looks correct, if there were any errors, or if the SQL query is correctly formed. Try:

filtered_school_models = SchoolFilter(request.GET, queryset=all_school_models)
print(filtered_school_models.data)
print(filtered_school_models.errors)
print(filtered_school_models.qs)
print(str(filtered_school_models.qs.query))

[loganknecht]

@rpkilby Here is what I see

<QueryDict: {'offset': ['49'], 'course_name': ['Nihongo']}>

<QuerySet [<SchoolModel: SchoolModel object (51)>]>
SELECT "piano_gym_api_schoolmodel"."id", "piano_gym_api_schoolmodel"."name", "piano_gym_api_schoolmodel"."school_board_id" FROM "piano_gym_api_schoolmodel" INNER JOIN "piano_gym_api_schoolcoursemodel" ON ("piano_gym_api_schoolmodel"."id" = "piano_gym_api_schoolcoursemodel"."school_id") WHERE UPPER("piano_gym_api_schoolcoursemodel"."name"::text) LIKE UPPER(%Nihongo%) ORDER BY "piano_gym_api_schoolmodel"."id" ASC

[loganknecht]

@rpkilby This looks like it might be an issue with my paginator implementation which looks like this

class HeaderLinkPagination(LimitOffsetPagination):
    default_limit = settings.DEFAULT_LIMIT
    max_limit = settings.DEFAULT_MAX_LIMIT
    min_limit = settings.DEFAULT_MIN_LIMIT
    min_offset = settings.DEFAULT_MIN_OFFSET
    max_offset = settings.DEFAULT_MAX_OFFSET

    def get_paginated_response(self, data):
        next_url = self.get_next_link()
        previous_url = self.get_previous_link()

        links = []
        header_data = (
            (previous_url, "prev"),
            (next_url, "next"),
        )
        for url, label in header_data:
            if url is not None:
                links.append("<{}>; rel=\"{}\"".format(url, label))

        headers = {"Link": ", ".join(links)} if links else {}

        return Response(data, headers=headers)

    def paginate_queryset(self, queryset, request, view=None):

        limit = request.query_params.get("limit")
        offset = request.query_params.get("offset")

        if limit is None:
            limit = settings.DEFAULT_LIMIT

        if offset is None:
            offset = settings.DEFAULT_OFFSET

        limit = int(limit)
        if limit > self.max_limit:
            error_message = ("Limit should be less than or equal to {0}"
                             ).format(self.max_limit)
            errors = {"limit": [error_message]}
            raise ValidationError(errors)
        elif limit < self.min_limit:
            error_message = ("Limit should be greater than or equal to {0}"
                             ).format(self.min_limit)
            errors = {"limit": [error_message]}
            raise ValidationError(errors)

        offset = int(offset)
        if offset > self.max_offset:
            error_message = ("Offset should be less than or equal to {0}"
                             ).format(self.max_offset)
            errors = {"offset": [error_message]}
            raise ValidationError(errors)
        elif offset < self.min_offset:
            error_message = ("Offset should be greater than or equal to {0}"
                             ).format(self.min_offset)
            errors = {"offset": [error_message]}
            raise ValidationError(errors)
        import pdb
        pdb.set_trace()

        return super(self.__class__, self).paginate_queryset(queryset, request, view)

If you see a solution, please let me know. Stay tuned while I try to shake this out.

[rpkilby]

My guess is that this is related to the given offset. There is only 1 object in the filtered queryset, but the request is telling the paginator to skip the first 49 items. If you were to check

print(filtered_school_models[49:])

I assume you'd get <QuerySet []>.

Closing, as it looks like this is a pagination issue.

[loganknecht]

Hey @rpkilby

I just want to say thank you so much for talking me through this. This wasn't even a django-filter problem. I was just being a dingus. You rubber ducking me is super super appreciated.

You are amazing, and once again, thank you for this amazing library!

[rpkilby]

No worries. Happy to help!