DRF cursor pagination with custom OrderBy

Question

I need to do some custom sorting on a Member model which looks like this (simplified):

class User():
    username = models.CharField()
    # ...

class Member():
    user = models.ForeignKey(User)  # not required
    invite_email = models.EmailField()
    # ...

I need to sort my queryset of Member in the following way:

Members that do not have a user set come last;
Sort by user username alphabetically (case insensitive), or invite_email if Member does not have a user.

I can do the appropriate sorting with django:

queryset.order_by(Lower('user__username').asc(nulls_last=True), 'invite_email')

I thought that I could override the order_by method to apply my custom ordering.

My problem comes with DRF cursor pagination. Reading from the doc, it seems like the ordering should be a string, or an unchanging value generally speaking. But when I use the Lower expression to order my queryset as needed, the ordering received in the cursor pagination class is an OrderBy object. Doing so, the paginate_queryset method is unusable.

I find it weird that you can't apply a custom filtering with the DRF pagination classes. Am I missing something?

Thanks in advance for any tips you might have!

Mathias Mathias · Accepted Answer · 2020-02-06T14:19:26

You may use a different kind of Pagination for your project. Why did you chose cursor pagination?

Details from the documentation:

https://www.django-rest-framework.org/api-guide/pagination/#cursorpagination

Cursor based pagination is more complex than other schemes. It also requires that the result set presents a fixed ordering, and does not allow the client to arbitrarily index into the result set. However it does provide the following benefits:

Provides a consistent pagination view. When used properly CursorPagination ensures that the client will never see the same item twice when paging through records, even when new items are being inserted by other clients during the pagination process. Supports usage with very large datasets. With extremely large datasets pagination using offset-based pagination styles may become inefficient or unusable. Cursor based pagination schemes instead have fixed-time properties, and do not slow down as the dataset size increases.

Further, if check the implementation of the cursor pagination you'll see:

...
    assert '__' not in ordering, (
        'Cursor pagination does not support double underscore lookups '
        'for orderings. Orderings should be an unchanging, unique or '
        'nearly-unique field on the model, such as "-created" or "pk".'
    )

...

    assert isinstance(ordering, (six.string_types, list, tuple)), (
        'Invalid ordering. Expected string or tuple, but got {type}'.format(
            type=type(ordering).__name__
        )

This implementation specifically disallows other order options than simple field based ordering using a string.

If you don't have any super large datasets, you probably wanna chose another pagination implementation like Page

Basically you can always create your own paginator class and overwrite paginate_queryset and add custom sorting.

DRF cursor pagination with custom OrderBy

1 Answers