how to set pagination in SQL query as per page?

Question

how to set pagination as per page in SQL query, i tried with django default pagination but it didn't worked in my code and i am direct fetching data using SQL raw-query instead of ORM. i think the another way is set pagination using SQL query LIMIT or OFFSET but i have no idea about url-endpoint when i am searching next-page.

class Order_ListAPIView(APIView):

    def get(self,request,format=None):

        if request.method == 'GET':
            cur,conn = connection()
            order_query = ''' SELECT * FROM orders'''
            order_detail_query = ''' SELECT * FROM order_details'''

            with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:

                cursor.execute(order_query)
                order_result = cursor.fetchall()
                order_data = list(order_result)

            ...
             ... #rest_code
              ...


            return Response({"order_data":order_data},status=status.HTTP_200_OK)
        else:
            return Response(status=status.HTTP_400_BAD_REQUEST)

Does this answer your question? [Best way to paginate a raw SQL query in a Django REST ListAPI view?](https://stackoverflow.com/questions/32191853/best-way-to-paginate-a-raw-sql-query-in-a-django-rest-listapi-view) — MK Patel, Mar 05 '20 at 10:04
cause my senior developer made decision to use sql-query method — anonymoususer, Mar 05 '20 at 10:17
seeing if request.method == 'GET': inside get() method is weird — Mahmoud Adel, Mar 05 '20 at 10:29
@anonymoususer you already in get() function so it doesn't make sense to check if request.method is GET because you already go to get() method when the request verb is GET — Mahmoud Adel, Mar 05 '20 at 10:34
i repeated that just because of to use custom Response. @MahmoudAdel — anonymoususer, Mar 05 '20 at 10:37
then make your custome response based on the data itself, check if the data returend being empty or not and then return your desired response — Mahmoud Adel, Mar 05 '20 at 10:40
now see this [link](https://pastebin.com/zijJUhAD) @MahmoudAdel — anonymoususer, Mar 05 '20 at 11:21

score 0 · Answer 1 · answered Mar 07 '20 at 17:33

I'm not too familiar with Django, but as you say, you're fetching the data raw yourself from the DB with MySQL, so to setup pagination it should be as simple as adding LIMIT $offset, $perpage to your MySQL query...

SELECT * FROM order_details LIMIT 0, 5;

This query, for instance, would get you PAGE 1, with 5 PER PAGE. To get to page 5 with 5 per page, it's LIMIT 4*5, 5 = LIMIT 25, 5. If you wanted to get to page 37 with 10 per page, it would be LIMIT 36*10, 10 = LIMIT 360, 10.

This should solve the pagination issue with the MySQL part. You'll need to setup vars to determine both page and perpage from the user, probably from a GET param.

how to set pagination in SQL query as per page?

1 Answers1