Server Side pagination using Data table

Mir

How can I achieve server side pagination using data table? Currently, on load of page I am initializing the data table & filling the table with Data (JSON) coming from database via Java servlet controller. I am making an Ajax call by passing search criteria in query string. Since we have lakhs of records, we have planned for server side pagination to improve performance.

For this the backend service developer has given me a service that gives me the per page records but takes inputs like PAGE NUMBER, NO OF PAGE RECORDS, SORT ORDER, SORT COLUMN.

I have to override data table implementation to pass these to service via Ajax request query string. I don't know if there is a way to achieve this. Please suggest!!!

Mir

I followed the example to test server side pagination, but it is not showing up the data in my table. Anything wrong?

var testTable;
function initTestTable(){
    testTable = $('#testTable').dataTable({
        
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "testTableData.html",
            "type": "GET",
        },
        "columns": [
            { "data": "masterObject.myData.code" },
            { "data": "masterObject.myData.description" }
        ]
   });
}

Here is my JSON data:

{"masterObject":{"draw":1,"myData":[{"code":"code1","description":"Desc1"},{"code":"code2","description":"Desc2"},{"code":"code3","description":"Desc3"},{"code":"code4","description":"Desc4"},{"code":"code5","description":"Desc5"},{"code":"code6","description":"Desc6"}],"recordsFiltered":74,"recordsTotal":210},"OUTPUT_STATUS":1}

allan

It doesn't appear to follow the requirements of server-side processing. There is no option to have the server-side information nested in another object.

If you require that, you will need to use ajax as a function and make a custom call to the server.

Allan

Mir

My JSON data was not in the reequired format as pointed by you. Also columns option was errorneous. Here is the changes that are working:

function initTestTable(){
     $('#testTable').dataTable({
 
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "testTableData.html",
            "type": "GET",
        },
        "columns": [
            { "data": "code" },
            { "data": "description" }
        ]
   });
}

And here is the JSON data:

{"data":[{"code":"code11","description":"Desc11"},{"code":"code21","description":"Desc21"},{"code":"code31","description":"Desc31"},{"code":"code41","description":"Desc41"},{"code":"code51","description":"Desc51"},{"code":"code61","description":"Desc61"},{"code":"code71","description":"Desc71"},{"code":"code81","description":"Desc81"}],"draw":"1","recordsFiltered":134,"recordsTotal":134}

Mir

How can I get data of one column into another column?. In below columnDefs option, I am not getting the second column value with full[1]. Instead it is showing as 'undefined'.

function initTestTable(){
     $('#testTable').dataTable({
  
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "testTableData.html",
            "type": "GET",
        },
      "columnDefs": [ {
                            "targets": 0,
                            "data": "code",
                            "render": function ( data, type, full ) {
                                return '<a href="'+data+'">'+data+'-'+full[1]+'</a>';
                              }
                          },
                          {
                            "targets": 1,
                            "data": "description",
                            "render": function ( data, type, row, meta ) {
                                return data;
                            }
                          }]
   });
}

allan

full[1] is trying to access an array, but you are giving it objects. So you would use full.description (for example).

Allan

Mir

And its working :)

Thanks Allan!

Mir

Allan,

Although I got the above sample working, I am facing difficulty in converting it to the client-side paging like pattern. Following are the code listings of my current client side paging data table which is perfectly working:

Table Init

$(document).ready( function () {
    myTable = $("#myTable")
       .dataTable(
           {
               scrollY:        "250px",
               scrollCollapse: false,
               jQueryUI:       true,
               "bJQueryUI": true,
               "bProcessing" : true,
               "bRetrieve" : true,
               "bDestroy" : true,
               "bPaginate": true,
              "bLengthChange" : true,
               "aoColumns" : [
                   {
                       "bVisible" : true,
                       "bSortable" : false,
                   },
                   {
                       "bSortable" : true,
                       "bVisible" : true,
                   },
                   {
                       "bSortable" : true,
                       "bVisible" : true,
                   }
                ]
           }
       );
});

Ajax Call (OnLoad after table initializaion/ Search record/Adding new record/ deleting a record)

   function loadMyTableData() {
            $.ajax({
                dataType : 'json',
                type : 'get',
                url : 'pullData.html',
                success : function(json) { 
                    myTable .fnClearTable();
                    myTable .fnAddData(getMyTableData(json));
                },
            });
        }

Input to fnAddData() function

function getMyTableData(json) {
    var aElements = new Array();
    aElements[0] = 'slNo';
    aElements[1] = 'Name';
    aElements[2] = 'Desc';
    var a = [];
    if(json != null && json.dataList != null){
        var dataListSize = json.dataList.length;      
        if(dataListSize > 0){           
            for ( var i = 0, iLen = dataListSize; i < iLen; i++) {
                var inner = [];
                for ( var j = 0, jLen = aElements.length; j < 3; j++) {
                    inner.push(json.dataList[i][aElements[j]]);
                }
                a.push(inner);
            }
        }
    }
    return a;
}

Now in my server side paging code, ajax is bound in table initialization itself. How can I separate it to make it look similar to my above client paging program. Basically I need to:

1. Only initilaize table with column properties & keep the ajax outside.
2. Clear table & fill it everytime I Add/Delete a record from table via ajax response using fnClearTable() and fnAddData() functions.

Please advise as I am trying things without success :(

allan

Can you link to the page so I can attempt to debug it please.

Allan

Mir

Allan,

I got it. I actually I had to do:

myTable.api().ajax.reload();

while this is working, load version of that is not working. Actually when I use the following line, the table is freezing with "Processing..." message. URL is fine & no error in developer tool console. What could be the issue:

myTable.api().ajax.url('getNextData.html').load();

myTable with server side = "true" is initilaised in earlier listings.