Facing django.core.exceptions.FieldError: Cannot resolve keyword `opportunity_count` into field.

Django Internals ORM
1

When upgrading Django from 3.0.6 to 3.2.12, I was unable to order response by annotated fields it is throwing Field error

views.py

class AccountViewSet(ViewSetMixin, viewsets.ModelViewSet):
    filter_backends = (AccountAnnotationFilterBackend, AccountFilterBackend,)
    ...

filters.py

class AccountAnnotationFilterBackend(BaseFilterBackend):

    def filter_queryset(self, request, queryset, view):
        relationship_id = request.query_params.get("relationship", "")

        if relationship_id:
            queryset = queryset..add_opportunity_count()
            return queryset

        return queryset

class AccountFilter(FilterSetMixin, drf_filters.FilterSet):
    relationship    = drf_filters.CharFilter(field_name='relationship', required=True)
    name            = drf_filters.CharFilter(field_name="name", lookup_expr="icontains")

    order_by_field = 'ordering'
    ordering_fields = {
        "name": "name",
        "opportunity_count": "opportunity_count",
    }
    default_ordering = ["name"]
    ordering = BaseOrderingFilter(fields=ordering_fields, default_ordering=default_ordering)
    
    class Meta:
        model = Account
        fields = ['relationship', 'source']
        base_filter_fields = ["relationship"]

managers.py

class AccountQuerySet(models.QuerySet):
    def add_opportunity_count(self):
        return self.annotate(
            opportunity_count=Coalesce(
                models.Subquery(
                    self.filter(id=models.OuterRef('id'))
                        .annotate(total_opportunity=models.Count('opportunity'))
                        .values('total_opportunity')[:1],
                    output_field=models.IntegerField()
                )
                , 0
            )
        )

class AccountManager(models.Manager.from_queryset(AccountQuerySet), CommonBaseManager):
    pass

filters/base.py

from django_filters.filters import OrderingFilter

class BaseOrderingFilter(OrderingFilter):

    def __init__(self, *args, **kwargs):
        self.default_ordering = kwargs.pop("default_ordering")
        super().__init__(*args, **kwargs)

    def filter(self, queryset, value):
        # Default Ordering if no ordering is passed
        if not value:
            value = self.default_ordering

        return super().filter(queryset, value[0])

Now when i call the API for AccountViewset with param ordering=opportuity_count it throws below exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/rest_framework/views.py", line 506, in dispatch
    response = handler(request, *args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/rest_framework/mixins.py", line 38, in list
    queryset = self.filter_queryset(self.get_queryset())
  File "/usr/local/lib/python3.8/site-packages/rest_framework/generics.py", line 150, in filter_queryset
    queryset = backend().filter_queryset(self.request, queryset, self)
  File "/usr/local/lib/python3.8/site-packages/django_filters/rest_framework/backends.py", line 96, in filter_queryset
    return filterset.qs
  File "/usr/local/lib/python3.8/site-packages/django_filters/filterset.py", line 243, in qs
    qs = self.filter_queryset(qs)
  File "/opt/services/djangoapp/src/prontosite/jointsolution/filters/mixins.py", line 51, in filter_queryset
    return super().filter_queryset(base_qs)
  File "/usr/local/lib/python3.8/site-packages/django_filters/filterset.py", line 230, in filter_queryset
    queryset = self.filters[name].filter(queryset, value)
  File "/opt/services/djangoapp/src/prontosite/jointsolution/filters/base.py", line 23, in filter
    return super().filter(queryset, value[0])
  File "/usr/local/lib/python3.8/site-packages/django_filters/filters.py", line 728, in filter
    return qs.order_by(*ordering)
  File "/usr/local/lib/python3.8/site-packages/django/db/models/query.py", line 1149, in order_by
    obj.query.add_ordering(*field_names)
  File "/usr/local/lib/python3.8/site-packages/django/db/models/sql/query.py", line 1993, in add_ordering
    self.names_to_path(item.split(LOOKUP_SEP), self.model._meta)
  File "/usr/local/lib/python3.8/site-packages/django/db/models/sql/query.py", line 1539, in names_to_path
    raise FieldError("Cannot resolve keyword '%s' into field. "
django.core.exceptions.FieldError: Cannot resolve keyword 'opportunity_count' into field. Choices are: ...

Seems in names_to_path method, it is not able to find annotated fields, Can anyone help on this?

2

It might help if you try to simplify the situation some to narrow down where the problem may be occurring.

For example, what happens if you try create and run a query in the Django shell that just consists of what add_opportunity_count does? Does it return a queryset with all the expected information?

3

Yes, correct. It returns the queryset with the expected information if i run in Django shell.

So basically if i try to get any annotated field’s value in filter_queryset method in AccountFilter class then it will not throw an error and will work as expected. It seems it is being done lazy loading for annotated fields

def filter_queryset(self, queryset):
    print(f"queryset[0].opportunity_count: {queryset[0].opportunity_count}")
    return super().filter_queryset(queryset)

Problem is that queryset has access to all the fields except annotated fields, but if we access any of the annotated field then all of the annotated fields are loaded inside queryset.