Pagination with server-side processing

sek001

I am trying to understand the server-side processing guidelines, but I’m missing something or something is getting lost in the translation to my individual situation. I have a large table generated server-side in a Python Flask framework (no database). At the moment, I’m initializing the DataTable by an Ajax request on page load, and that works fine. I can also initialize it after page load when the user chooses some options and then clicks a “Generate Table” button. But here is where I’m getting a little lost. According to the manual “DataTables will send the following data [draw, start, length, etc.] in order to let the server know what data is required”. How is this data sent (or, how do I access it server-side)? Or do I have to add it to the data object myself?
Separately, when I return the data, I use

{
    "draw": 2,
    "recordsTotal": 8528, // The number in my test data set
    "recordsFiltered": 8528,
    "data": [
        ["apples", "oranges", "pears"],
        ["bananas", "strawberries", "cherries"]
        ...
    ]
}

However, the resulting table contains all the records and no pagination. I have tried using “deferLoading” and adding the first ten rows in the html. This gives me pagination options, but the first page is empty, the second page has the entire table, and the rest of the page buttons don’t work.

I feel like I’m missing something obvious. Perhaps the problem is related to the page().draw() is not refreshing the rows on the table issue?

glenderson

When doing server side processing, you (i.e. the server and the code you wrote), does all the work and dataTable only displays what you pass.

For instance, you have to pass the total # of records, the total # of filtered records, and only the records you want to display. Your backend needs to handle all the pagination also through some limit mechanism. Just let me caveat here, I'm writing all of the backend code myself, using nothing provided by dataTables for php.

So, my ajax page receives a request from dataTables to display records 200-300 with a filter and column sort order.

I have to write the query (programmatically of course), to start at record 200, limit it to 100 records, and provide the sort order in the sql order by and the "where" clause based upon filter. All these setup values are passed in the URL my ajax page from dataTables.

I would only use server side processing for very large amounts of data or if all your data is not obtainable in a single query statement (i.e., you need to pull data from multiple data sources to render all columns. Typically, you don't need server side processing, all you need to do is limit the number of records being displayed to end up with a very responsive application. For the example you show, I would not recommend server side if those 3 fields all some from a single query statement. The 10,000 record should load rather quickly has been my experience.

sek001

I don't have a database--just a very large array (it contains word counts for texts such as collections of novels). But it is easy to slice that up on the server side and send a subset of the data back in the Ajax response. My difficulty is in figuring out the client-side settings to display it with the correct pagination. Here's a sample of how I'm initiating the table:

    var myTable = $('#example').DataTable({
        "serverSide": true,
        "processing": true,
        "paging": true,
        "searching": { "regex": true },
        "lengthMenu": [ [10, 25, 50, 100, -1], [10, 25, 50, 100, "All"] ],
        "pageLength": 10,
        "ajax": {
            "type": "POST",
            "url": "/getTable",
            "dataType": "json",
            "contentType": 'application/json; charset=utf-8',
            "data": function (data) {
                // Grab form values containing user options
                var form = {};
                $.each($("form").serializeArray(), function (i, field) {
                    form[field.name] = field.value || "";
                });
                // Add options used by Datatables
                var info = { "start": 0, "length": 10, "draw": 1 };
                $.extend(form, info);
                return JSON.stringify(form);
            },
            "complete": function(response) {
                console.log(response);
           }
        }
    });

This works fine when I initiate the table. I can count the number of rows server-side and return recordsTotal in the response. Perhaps I'm not entirely clear on what should go in recordsFiltered. I'm also not sure how I re-send the ajax request when the user clicks on a new page number, changes the number of records displayed per page, or initiates a search. How do I update the data in the info variable? And, once the the ajax request has responded with a new set of data, how to redraw the table to display the new page.

Thanks for your help!

glenderson

The ajax page should be the page that is carving up the data. If you monitor your requests and responses, if the user clicks page "3" for instance, the get URL or post parameters will contain &iDisplaystart and &iDisplaylength

If you are displaying 50 records at a time, &iDisplaylength will be 50.

Ff the user clicks "3", &iDisplaystart will be 100
(Page 0 starts at 0, Page 1 at 50, page 2 at 100, page 3 ...)

Your ajax page must be able to read the request variables and contain logic to send back 50 records (read from the request variables), starting at record 100 (read from the request variables). Also included in the URL are the column sort order, and filter value if being used.

The start of your json response should be something like

{ "iTotalRecords":  # of total records in your data set ,
"iTotalDisplayRecords":  # of total records that match the filter or search value, 
"data": [
... your jSON data starting at record 100, and running for 50 records
]
}

sek001

Thanks. This pretty much confirmed my suspicion that I was hijacking the POST data with my form values and not adding in the pagination info to my request data. I've changed line 20 in the code from my previous post to

var info = (myTable == null) ? { "start": 0, "length": 10 } : myTable.page.info();

In short, this works (at least for pagination and page length--I haven't set up any server-side filter processing yet).

To help out anyone else who is struggling with server-side processing, note that the previous answer uses the legacy parameters "iTotalRecords" and "iTotalDisplayRecords". With DataTables 1.10+, the parameters should be "recordsTotal" and "recordsFiltered", which worked for me. See the Legacy section at https://datatables.net/manual/server-side.

I have one follow-up question. Everything seems to be working for me now without passing the draw parameter to the server. Is there any reason I should be using it? And if so, how do I access the current draw to add to my ajax request.

Thanks for your help!

glenderson

Thank you for pointing that I was still using legacy terms here! I just upgraded my API for ajax to use the 1.10 terms and methods, appreciate it!