How to connect DataTables Paging with Django LimitOffsetPagination

mloo

Description of problem:
We would like to use DataTables to pull back pages from a Django REST API defined to work like this...
https://www.django-rest-framework.org/api-guide/pagination/#limitoffsetpagination

Django offers multiple ways to paginate, but after studying:
https://datatables.net/manual/server-side

... it seemed the LimitOffsetPagination would be the most compatible with DataTables so I had our Django team switch to using that methodology where Limit and Offset are passed to them as query string parameters. We get back JSON containing Count (Total number of records), Next (page of data link), Previous (page of data link), and Results (the data itself in object form). My thought was that the Offset and Limit parameters would somehow align with DataTable's Start and Length variables.

There are cases where there will be tens of thousands of object data points that come back. What's the strategy and the documentation to go with it? I'm having a lot of trouble determining the building blocks we need and the glue that ties these two ends together. We even coded our own adaptor to go between the Table and Django in order to include Token authentication strings with each request.

So for each page of data has this round trip:
DataTables in PHP -> Custom Adaptor Code in PHP -> Django Rest API -> Custom Adaptor Code in PHP -> DataTables in PHP

Doable? What are the components I need to assemble and master to get this connected end to end?

kthorngren

I haven't used server side processing with Django and haven't used the rest framework you linked to. You can take a look at the django-serverside-datatable 1.0.0 library to see what they have done. I haven't used it so not sure how well it works. Also you can look at the SSP PHP script used in the examples.

It does seem the start and length parameters will work with the limit and offset paging of the REST API. In addition you will need to handle the draw parameter. It is used as a sequence number for the requests and responses. Not sure how to handle that in your REST framework. You will need to decide about supporting the search and order parameters. You can ignore them or restructure them to work with the REST API framework.

You will need to provide the Return Parameters. Datatables uses them for display of the info element and the paging buttons and probably for internal calculations.

You can use ajax.data to modify the parameters sent to the server and ajax.dataSrc to modify the returned parameters to what Datatables expects.

Another option that might be easier is to not use server side processing and to use ajax.data to send the limit and offset parameters along with any search/sort parameters you want to support. You can create your own inputs for this. Then you won't need to worry about the Datatables specific parameters like draw. See this example for sending parameters. When one of the inputs is changed use ajax.reload() to send another ajax request to the server.

Kevin

mloo

Thanks Kevin. So the difference between serverside and and not server side... Will that affect the total number of paging buttons? I'm guessing either way the total number of paging buttons is determined by the total number of records divided by the length of the pages. I'm guessing that the server side model would use the total and length to create the right amount of buttons and then each button would provide the start and length for subsequent calls. On the "Not serverside" route would we need to be building our own model to support telling datatables how to manage the paging buttons? I'm not clear on how the user would be able to ask for additional pages in the "not-serverside" model. For example, 70,000 records are identified and we want to move from the 5th page of 100 records to the 6th page of 100 records. Will serverside and non-serverside both know there are 70,000 records in total so that the appropriate quantity of paging buttons can be displayed?

mloo

Having gotten over a lot of this hurdle, there are a couple things that helped me tremendously in figuring out how all this works.

1. Setup an ajax: { url: "YourPage.php" ... and then set breakpoints on that page to see what DataTables is sending you in your IDE debugger. Once you see what you are receiving from DataTables, things start to click.
2. Open up your developer Tools in your browser and watch the data getting passed back and forth. Seeing that neatly formatted data was a real eye opener, aha moment.