Is there any way I can use objs = Model.objects.raw(sql) with PageNumberPagination in django rest framework without passing list(objs) to pagination

I'm currently facing a challenge with pagination while using raw SQL queries in Django. The situation arose when I needed to apply an additional filter in the LEFT OUTER JOIN of my query, which I found wasn't directly supported by Django's ORM. Despite attempting to use FilteredRelation, I realized that it didn't fully meet my requirements.

As a workaround, I opted to use raw SQL queries, which allowed me to achieve the desired filter. However, I encountered a roadblock when attempting to paginate the results. It seems that raw querysets don't support pagination out of the box.

Here's a snippet of the relevant code:

queryset = CheckTraModelTotal.objects.raw(sql_query)
page = RawQuerySetPaginator(queryset, 15)
serializer = self.get_serializer(page, many=True)
return self.get_paginated_response(serializer.data)

I think you just need to change how you call pagination, if your code sample is accurate: page = RawQuerySetPaginator(queryset, 15)

Pagination should have no issue with most raw queries. Ordering and searching are, of course, an issue.

class MyPagination(PageNumberPagination):
    page_size = 1

qs = User.objects.raw("select email from user order by id desc")

paginator = MyPagination()

# page is a list of user objects here
page = paginator.paginate_queryset(qs, request=req)

# generate the list of dict objects for output
serialized_data = UserSerializer(page, many=True).data

# create a Response(), with the `count` and next/prev links
output = paginator.get_paginated_response(serialized_data)

Paginators are confusing, since they internally use a django paginator, and thus have a page, which has a paginator, which does the .count. How that does it I don't know, it's magic. RawQuerySet does not have a count() method but this still works somehow.

print(f"total records => {paginator.page.paginator.count}")

It may help you in testing, so here is the snippet I use to generate fake requests - you can't just use the output of the APIRequestFactory directly.

def make_request():
    from rest_framework.test import APIRequestFactory
    from rest_framework.request import Request
    from rest_framework.parsers import JSONParser
    req = APIRequestFactory().get("/", {"page": "1"})
    return Request(req, parsers=[JSONParser()])
Back to Top