Django Rest Framework Cursos pagination with multiple ordering fields and filters

ordering can (and often should!) be multiple fields.

But you're right that only the first field is stored in the cursor token. However, the other fields are still applied:

# rest_framework/pagination.py

class CursorPagination(BasePagination):
    [...]

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

        '''
        The below part is where the ordering fields come into play.
        They are not stored in the token, instead they are reapplied 
          on every hit of the paginator.
        '''
        # Cursor pagination always enforces an ordering.
        if reverse:
            queryset = queryset.order_by(*_reverse_ordering(self.ordering))
        else:
            queryset = queryset.order_by(*self.ordering)

So this part of the investigation is a red herring.

What I'm willing to bet is the culprit is the fact that your ordering list contains date twice. I haven't tested it, but there's a chance that including the same field twice leads to some kind of nonsense SQL being generated at some point in this operation. It might also be data-dependent for all I know.

Try removing the duplicate date.

Alternatively, inspect what .order_by() produces for you and/or look at the raw SQL to verify that the query isn't malformed.

I manage to solve this by using another module `django-cursor-pagination`, and wrapping the paginator from that module in a Pagination class, as required by DRF:

https://pypi.org/project/django-cursor-pagination/

# CODE IS MOSTLY GENERATED BT CHAT GPT
from rest_framework.pagination import BasePagination
from rest_framework.response import Response
from rest_framework.utils.urls import replace_query_param, remove_query_param

from cursor_pagination import CursorPaginator


class CustomCursorPagination(BasePagination):
    page_size = 10
    ordering = None
    after_query_param = 'after'
    before_query_param = 'before'
    page_size_query_param = 'page_size'
    max_page_size = 100

    def get_page_size(self, request):
        try:
            size = int(request.query_params.get(self.page_size_query_param, self.page_size))
            if self.max_page_size:
                return min(size, self.max_page_size)
            return size
        except (TypeError, ValueError):
            return self.page_size

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

        self.after = request.query_params.get(self.after_query_param)
        self.before = request.query_params.get(self.before_query_param)
        page_size = self.get_page_size(request)

        self.paginator = CursorPaginator(queryset, ordering=self.ordering)

        if self.after and self.before:
            raise ValueError("Both 'after' and 'before' parameters cannot be used together.")

        if self.after:
            self.page = self.paginator.page(first=page_size, after=self.after)
        elif self.before:
            self.page = self.paginator.page(last=page_size, before=self.before)
        else:
            self.page = self.paginator.page(first=page_size)

        self.has_next = self.page.has_next
        self.has_previous = self.page.has_previous

        self.next_cursor = self.paginator.cursor(self.page[-1]) if self.has_next else None
        self.prev_cursor = self.paginator.cursor(self.page[0]) if self.has_previous else None

        return list(self.page)

    def _build_url_with_cursor(self, param, cursor_value):
        url = self.request.build_absolute_uri()
        url = remove_query_param(url, self.after_query_param)
        url = remove_query_param(url, self.before_query_param)
        return replace_query_param(url, param, cursor_value)

    def get_next_link(self):
        if not self.has_next or not self.next_cursor:
            return None
        return self._build_url_with_cursor(self.after_query_param, self.next_cursor)

    def get_previous_link(self):
        if not self.has_previous or not self.prev_cursor:
            return None
        return self._build_url_with_cursor(self.before_query_param, self.prev_cursor)

    def get_paginated_response(self, data):
        return Response({
            'next': self.get_next_link(),
            'previous': self.get_previous_link(),
            'results': data
        })

    def get_schema_operation_parameters(self, view):
        return [
            {
                'name': self.after_query_param,
                'required': False,
                'in': 'query',
                'description': 'Cursor to fetch the next page of results.',
                'schema': {'type': 'string'},
            },
            {
                'name': self.before_query_param,
                'required': False,
                'in': 'query',
                'description': 'Cursor to fetch the previous page of results.',
                'schema': {'type': 'string'},
            },
            {
                'name': self.page_size_query_param,
                'required': False,
                'in': 'query',
                'description': f'Number of results per page (max {self.max_page_size})',
                'schema': {'type': 'integer'},
            }
        ]

And than use that class as the basis for my RevenuePagination class:

class RevenuePagination(CustomCursorPagination):
    page_size = 500
    max_page_size = 500
    ordering = ['date', 'country_name', 'platform_type_code', 'custom_channel_name', 'id']