Unclear Usage For OrderingFilter and Calculated Fields

[loganknecht]

Gratitude

Hello there!

It has been such a pleasure using this library! I'm so thrilled about all the convenience it offers!

Thank you for that!

Goal

My goal right now is I want to have an endpoint that supports search-ability on model parameters, and ordering as well.

Data Model

I have a School model that has a calculated field on it called learner_enrolled_count

The JSON response looks something like this:

{
    "schools": [
        {
            "id": 6,
            "name": "Piano Gym Six",
            "courses": [
                // ...
            ],
            "learner_enrolled_count": 0
        },
        {
            "id": 7,
            "name": "Piano Gym Seven",
            "courses": [
                // ...
            ],
            "learner_enrolled_count": 5
        }
    ]
}

The learner_enrolled_count is a calculated field.

The Problem

I have read the documentation here:
https://django-filter.readthedocs.io/en/stable/ref/filters.html?highlight=order#orderingfilter
and here:
https://django-filter.readthedocs.io/en/stable/ref/filters.html?highlight=order#adding-custom-filter-choices

So based on that I wrote this filter set here:

# ------------------------------------------------------------------------------
# Python Standard Libraries
# ------------------------------------------------------------------------------
# N/A
# ------------------------------------------------------------------------------
# Third-party Libraries
# ------------------------------------------------------------------------------
from django_filters import CharFilter
from django_filters import OrderingFilter
from django_filters.rest_framework import FilterSet
# ------------------------------------------------------------------------------
# Custom Libraries
# ------------------------------------------------------------------------------
from piano_gym_api.versions.v1.models.school_model import SchoolModel

# See:
# https://django-filter.readthedocs.io/en/stable/ref/filters.html#adding-custom-filter-choices
class SchoolOrderingFilter(OrderingFilter):

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.extra["choices"] += [
            ("learner_enrolled_count", "Learner Enrolled Count"),
            ("-learner_enrolled_count", "Learner Enrolled Count (descending)"),
        ]

    def filter(self, query_set, values):
        if(values is None):
            return super().filter(query_set, values)

        for value in values:
            if value in ['learner_enrolled_count', '-learner_enrolled_count']:
                return query_set.order_by(value)

        return super().filter(query_set, values)


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")
    # ---
    lesson_name = CharFilter(field_name="school_lesson__name",
                             lookup_expr="icontains")
    lesson_description = CharFilter(field_name="school_lesson__description",
                                    lookup_expr="icontains")
    # ---
    flash_card_set_name = CharFilter(field_name="school_lesson__flash_card_set__name",
                                     lookup_expr="icontains")
    flash_card_set_description = CharFilter(field_name="school_lesson__flash_card_set__description",
                                            lookup_expr="icontains")
    # ---
    headmaster_username = CharFilter(field_name="school_board__school_board_headmaster__learner__user__username",
                                     lookup_expr="icontains")
    board_member_username = CharFilter(field_name="school_board__school_board_member__learner__user__username",
                                       lookup_expr="icontains")

    # See:
    # https://django-filter.readthedocs.io/en/stable/ref/filters.html#orderingfilter
    o = SchoolOrderingFilter(
        # tuple-mapping retains order
        fields=(
            ("learner_enrolled_count", "learner_enrolled_count"),
        ),

        # labels do not need to retain order
        field_labels={
            "learner_enrolled_count": "Total learners enrolled in school",
        }
    )

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

This issue is that it doesn't seem to be ordering at all! I have no idea why. It's so strange.

If I drop a debug trace into the filter method of SchoolOrderingFilter I see that values is None. I'm not sure what that should be.

The request I'm making looks like this
{{API_URL}}/api/v1/schools/?offset=5&limit=3&ordering=learner_enrolled_count

And the view that receives this request looks like this:

class SchoolViewSet(ViewSet):
    # ...

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

        school_filter = SchoolFilter(request.GET, queryset=all_school_models)

        paginator = HeaderLinkPagination()
        current_page_results = paginator.paginate_queryset(school_filter.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 Questions

I think it's really unclear for me in the documentation on how to use the filtering feature AND how to use the calculated field ordering.

What am I doing wrong? Am I misunderstanding this functionality? I feel like I'm performing the correct steps for this, but just can't seem to get the ordering functionality of this library working!

Again, thanks for everything!

[carltongibson]

Hi @loganknecht. I have to say, thank you for such a wonderful issue report. It starts well, and just gets better. Just so clear. So thank you.

First off: it looks like you defined the SchoolOrderingFilter with the field name o:

o = SchoolOrderingFilter(...)

So when you say:

The request I'm making looks like this
{{API_URL}}/api/v1/schools/?offset=5&limit=3&ordering=learner_enrolled_count

I´m expecting the query string parameter to be o too. Does this work: {{API_URL}}/api/v1/schools/?offset=5&limit=3&o=learner_enrolled_count?

Then (the bit I´m unsure about in your report) you say it´s a computed field -- what exactly do you mean? i.e. is it a field that appears on the model, or is it a Python property say?

Another way of asking (part of) that is does order_by() on the queryset work with this field? i.e. does this work:

SchoolModel.objects.filter(...).order_by(learner_enrolled_count)

[loganknecht]

@carltongibson That is quite interesting! I did not expect o to be the defined ordering parameter! I did not understand that from the documentation 😂

Using the o parameter

What's interesting is if I use the query
{{API_URL}}/api/v1/schools/?offset=5&limit=3&o=learner_enrolled_count

I get these results:

{
    "schools": [
        {
            "id": 6,
            "name": "Piano Gym Six",
            "courses": [
                # ...
            ],
            "learner_enrolled_count": 0
        },
        {
            "id": 8,
            "name": "Piano Gym Eight",
            "courses": [
                # ...
            ],
            "learner_enrolled_count": 0
        },
        {
            "id": 9,
            "name": "Piano Gym Nine",
            "courses": [
                # ...
            ],
            "learner_enrolled_count": 0
        }
    ]
}

The interesting part about this, is now the one school I want to be ordered at the top is not in this search result. Which means that this was likely ordered but pagination is offsetting the results still. However when I used learner_enrolled_count it's at the last spot.
{{API_URL}}/api/v1/schools/?limit=3&o=learner_enrolled_count

So I used tested it using the -learner_enrolled_count (removing the offset and sorting by descending)
{{API_URL}}/api/v1/schools/?limit=3&o=-learner_enrolled_count
and got this response

{
    "schools": [
        {
            "id": 7,
            "name": "Piano Gym Seven",
            "courses": [
                # ...
            ],
            "learner_enrolled_count": 1
        },
        {
            "id": 1,
            "name": "Piano Gym",
            "courses": [
                # ...
            ],
            "learner_enrolled_count": 0
        },
        {
            "id": 2,
            "name": "Piano Gym Two",
            "courses": [
                # ...
            ],
            "learner_enrolled_count": 0
        }
    ]
}

It looks like it worked!

Computed Field

learner_enrolled_count does not exist on the model itself. I used an annotation to calculate this and inject it into the results.

At risk of diving into the data model too much, this endpoint is meant to be a singular entry point for filtering and ordering queries for a list of schools.

I have to optimize this endpoint using model retrieval like this:

class SchoolModelManager(Manager):
    def getBrowseSchoolsData(self, *args, **kwargs):
        """Return all Schools that contain lessons with flash card sets.

        Does not exclude empty sets, just requires that the school has something
        to enroll in
        """
        # WARNING: This MUST be imported here otherwise the compilation fails
        #          because of circular dependencies
        from piano_gym_api.versions.v1.models.flash_card_model import FlashCardModel
        # from piano_gym_api.versions.v1.models.flash_card_model import PlaySheetMusicFlashCardModel
        # from piano_gym_api.versions.v1.models.flash_card_model import TrueOrFalseFlashCardModel
        # from piano_gym_api.versions.v1.models.flash_card_set_model import FlashCardSetModel
        from piano_gym_api.versions.v1.models.sheet_music_model import SheetMusicModel
        # import pdb
        # pdb.set_trace()

        # --------------------
        sheet_music_query_set = (SheetMusicModel.objects.all()
                                 .select_related("school"))
        play_sheet_music_flash_card_sheet_music_prefetch = Prefetch("playsheetmusicflashcardmodel__sheet_music",
                                                                    sheet_music_query_set)
        # --------------------
        flash_card_query_set = (FlashCardModel.objects.all()
                                .select_related("flash_card_set",
                                                "playsheetmusicflashcardmodel",
                                                "school",
                                                "trueorfalseflashcardmodel")
                                .prefetch_related(play_sheet_music_flash_card_sheet_music_prefetch))
        flash_card_prefetch = Prefetch("flash_card_set__flash_card", flash_card_query_set)
        # --------------------
        school_lesson_query_set = (SchoolLessonModel.objects.all()
                                   .select_related("course",
                                                   "flash_card_set",
                                                   "school")
                                   .prefetch_related(flash_card_prefetch))
        school_lesson_prefetch = Prefetch("school_lesson", school_lesson_query_set)
        # --------------------
        school_course_query_set = (SchoolCourseModel.objects.all()
                                   .select_related("school")
                                   .prefetch_related(school_lesson_prefetch))
        school_course_prefetch = Prefetch("school_course", school_course_query_set)
        # --------------------
        query_set_to_return = (SchoolModel.objects.filter(school_lesson__flash_card_set__isnull=False)
                               .distinct()
                               # .annotate(learner_enrolled_count=Count("learner_enrolled_school", distinct=True))
                               .annotate(learner_enrolled_count=Case(
                                   When(learner_enrolled_school__learner_enrolled_course__learner_enrolled_lesson__is_enrolled=True,
                                        then=1),
                                   default=0,
                                   output_field=IntegerField())
        ).prefetch_related(school_course_prefetch))

        return query_set_to_return

The calculated field bit is in the query_set_to_return variable. What it does is takes the count of total learners enrolled and annotates it to the field learner_enrolled_count. I have no idea if this is the correct way to achieve this, but it does seem to work 😂

Suggestion

One suggestion I would make is to clarify in the documentation, either using actual url examples for requests or simple text and explain that the o in the examples is what is exposed as a parameter for ordering.

Conclusion

It does appear to be working correctly now that you clarified the o parameter is what the ordering filter gets assigned to!

If the querysets I'm providing for the filter have an annotated field in them already - it looks, from my testing, that I am not obligated to making my own custom OrderingFilter.

So because of that I think I can just use a simple OrderingFilter instead!

Please correct me if I'm wrong!

I believe that solves my confusion! Thank you!

[carltongibson]

Hi @loganknecht. Super, looks like you've got it working. 💃

If you're using an annotation then you should be able to filter and order on that with the standard class yes.

I'll make a tweak to the docs.

Thanks for your input.

[loganknecht]

@carltongibson and everyone else, thanks for the amazing library!