Django DRF incorrect pagination count

Question

I use a custom yet simple pagination class for my django drf application:

from rest_framework.pagination import PageNumberPagination

class CustomPagination(PageNumberPagination):
    page_size = 10
    page_size_query_param = "page_size"
    max_page_size = 100

I have also a ViewSet action that does some operation on the inherited queryset and return the result paginated:

class MatchOfJobViewSet(ModelViewSet):

  serializer_class = MatchSerializer
  filter_backends = (DjangoFilterBackend,)

  # classic filter inheriting from django_filter.FilterSet
  filter_class = MatchFilter

  def get_queryset(self):
    job_pk = self.kwargs["job_pk"]
    return Match.objects.filter(job_pk=job_pk)

  @action(
    detail=False,
    methods=["get"],
    url_path="dynamic",
    serializer_class=MatchDynamicSerializer
  )
  def dynamic(self, request, *args, other_args, **kwargs):
    """return the list of matches with a dynamic serializer"""

    # some operation on the queryset
    queryset = self.get_queryset().select_related("some_field")

    # apply the filters
    queryset = self.filter_queryset(queryset)

    # paginate
    page = self.paginate_queryset(queryset)

    # I use a custom serializer that takes extra arguments
    # to update dynamically the fields to be serialized
    serializer = self.get_serializer(page, many=True, other_args=other_args)

    # unchanged from the generic list response
    return self.get_paginated_response(serializer.data)

The response I get from the view is like this

{
  "count": 308,
  "next": "https://blabla.com?page=2&page_size=100",
  "previous": "https://blabla.com?page=4&page_size=100",
  "results": [...]
}

The count indicates 308 entries although there are only 297 entries when I check in the database (which causes the last page to end up empty)

From what I read the count parameter is computed by the Paginator (which is the default DjangoPaginator) by using the same queryset. How can the evaluated queryset and the Paginator.count() give different results?

In the table it may be 297 rows, that doesn't mean that the QuerySet.count() in the pagination class must return the same number. That is, your QuerySet may contain some duplicate rows. — JPG
I can see you have been using the filter_backends, which may cause the duplication, somehow — JPG
Show us the MatchFilter, I believe something happens there. Like filtering many to many relationships and getting multiple duplicate results. — Davit Tovmasyan
This is true, the filters might duplicate the entries. I will check if adding distinct(id) changes anything. However it there was duplicate rows the last page wouldn't be empty, right? — Martin Faucheux

Martin Faucheux Martin Faucheux · Accepted Answer · 2021-02-19T09:21:11

Thanks to the comment I found that the discrepancy between queryset.count() and len(queryset) where caused by some annotations applied in my get_queryset() method.

I thought using queryset.query.annotations.clean() would totally clean it from the SQL query but it left some traces, notably some OUTTER LEFT JOIN. This resulted into entries counted multiple times by the COUNT SQL query.

If you end up having similar issues, I'd recommend to double check the filters and annotation applied to your queryset. You can inspect the SQL query run by .count() using the method from this ticket

Django DRF incorrect pagination count

1 Answers