I have been exploring  jquery Datatable grid for a project I am working on. Initially I was using client-side configuration. But for about 5000 rows, performance started affecting. Datatable had Noticeable delays while pagination. So, I decided to use server-side configuration.

Using server-side configuration for data table configuration, I faced following issues:

1) Datatbles.net has documentation for server-side configuration, but it is too confusing. Many parameters with the similar names doesn’t help the cause.
2) The default Export action buttons (excel/print/pdf) in datatable, only return the data  loaded on the client side.
a) In client-side configuration, whole data is loaded into the table once from ajax url. So all data would be exported in excel.
b)In server side configuration, data is laoded page wise. CLient table holds data only for current page. Hence, Excel report returns data only of current page.

Thus, I am writing this article to make it easier. It would help someone interested to using server-side configuration for datatable. I am splitting this article in two.
1. jquery configuration for using server-side datatbles
2. Server side controllers for handling datable requests..

1. Jquery configuration for data tables with server-side paging

function configureListDataTable(datatable,columns) {
    var latest_data;
    $(datatable).DataTable( {
        "lengthChange": false,
        "aaSorting": [],
        "columns" : columns,//setting columns dynamically using function getListTableColumns mentioned below 
        //End
        "dom": 'Bfrtip',
        "buttons": [
            'colvis',{
                /* as mentioned earlier in the post, the basic functionality of datatble export buttons (excel,print) return only current page data while using
                 * server side configuration... In order to get complete data we need to extend functionality of buttons 
                */
                extend: 'excelHtml5', // denotes we are extending excel button
                action: function (e, dt, node, config ){ // here dt is datatbale object
                    var this_o = this;
                     exportExtension = 'Excel';                            
                    var url = "";
                    var data = { limitPerPage: "10", pageNumber: "1",sortBy:"_id",sortOrder:"-1" };
                    data = latest_data; // copying data reuest sent to last paging call.. doing this we can have data of all column filters
                    data.action = "excel"; // sending an additional parameter to identify it is from action button and not from datatable page request
                    // ajax call to get data for excel export
                    /* based on action parameter.. in database query we can skip paging limit part and return all rows 
                       satisfying filter criteria */
                    jQuery.ajax({
                           async: false,
                          dataType: 'json',
                          type: 'POST',
                          evalScripts: true,
                          url: url,
                          data: "data="+JSON.stringify(data),
                          success:function (json) {                                        
                              var totalDataArray = [];
                             $.each(json.records,function(objindex,object){
                                 // can perform client side operation on data to be returned..
                                 totalDataArray.push(object);
                             });                                        
                            dt.clear(); // this need to be cleared else data of current page displayed in datatble included twice(as its already loaded on client side)
                            dt.rows.add(totalDataArray); // adds returned data as data to be exported
                            $.fn.DataTable.ext.buttons.excelHtml5.action.call(this_o, e, dt, node, config); // calling excel export function with comlete data..
                          }                          
                          });
                        }
                    }, {
                // extension of print button is almost similar to excel button
                // similarly, you can extend csv,pdf buttons also
                extend: 'print',// denotes we are extending print button
                action: function (e, dt, node, config ){
                    var this_o = this;                             
                    var url = "";
                    var data = { limitPerPage: "10", pageNumber: "1",sortBy:"_id",sortOrder:"-1"};
                    data = latest_data;
                    data.action = "print";//specifying print action
                    // ajax call to get data for print export
                    jQuery.ajax({
                           async: false,
                          dataType: 'json',
                          type: 'POST',
                          evalScripts: true,
                          url: url,
                          data: "data="+JSON.stringify(data),
                              success:function (json) {                                                                             
                                var totalDataArray = [];
                                 $.each(json.records,function(objindex,object){                                         
                                     totalDataArray.push(object);
                                 });                                        
                                dt.clear();
                                dt.rows.add(totalDataArray);
                                $.fn.DataTable.ext.buttons.print.action.call(this_o, e, dt, node, config);
                              }                          
                          });
                        }
                    }                                        
                ],
        "oLanguage": {
            "sLengthMenu": "_MENU_ records",
            "sEmptyTable": "No data available in table"
        },            
        "processing": true, // this displays a loader if it takes time for loading records..
        // number of records per page
        "iDisplayLength": 20,
        // enables serverside configuration
        "serverSide": true,
        "pagingType": "full_numbers",
        // ajax call for datatable page request
        "ajax":{
            url :"", // json datasource
            type: "POST",  // method  , by default get
            // default configuration 
            data : { limitPerPage: "10", pageNumber: "1",sortBy:"_id",sortOrder:"-1"},
            data :  function (data, settings) {
            // Modify query parameters to match my API
                var info = $(datatable).DataTable().page.info();
                data.sortBy = "_id";
                data.sortOrder = "-1";
                latest_data = data;
                data = "data="+JSON.stringify(data);
                return data;
            },
            dataType: 'json',
            dataSrc: function (json) {
                /* when ajax request returns pagedata we can perform client side operation on the data before displaying it in the 
                 * table.. here you can see json is data received from the page.. then
                 * I am returning my records in  'records' key in data hence accessing it as json.records   */
                var totalDataArray = [];
                if(json.records != undefined){
                      $.each(json.records,function(objindex,object){
                        // perform operations on object data
                          totalDataArray.push(object); // then insert object/row in array
                      });
                      return totalDataArray; // returning data rows to be displayed in datatable for current page
                }
            },
            error: function(){
                $(datatable).append("No data available in table");
                } 
            }, 
        "drawCallback": function( settings ) { // calling this function on each draw of the table /* you can use draw callback function if you want to perform some action on each draw of page * i.e. each time page of table changed or filter is applied.. etc.. this function called * In my application, I needed to set links for element in current drawn page so * called below function pagination handler for doing the same * note:paginationhandler is my custom function. It is not part of data table library */ 
        $().paginationhandler(this); 
} } );
}

Table columns defined as follows:

// here fields is list of columns I am getting via server request.. as column names are also dynamic
function getListTableColumns(fields,selectedPageName){
    var columns = [];
    $.each(fields,function(index,field){
            var columnObj = new Object();
            columnObj.name = field.fieldId;
            columnObj.position = field.position;
            columns.push(columnObj);
    });
    return columns;
}

For adding individual column filter you would need below code:

// this is for adding input text field below each column footer
$('.datatable tfoot th').each( function () {
                var title = $(this).text();
                $(this).html( '<input type="<text>" placeholder="Search '+title+'" style="width:85%;"/>' );
            } );

// here we are binding individual column search fields to data table draw request
var table = $(options.container).find(".").DataTable();
table.columns().every( function () {
    var that = this;
    this.search.filter = "$lt";
    $( 'input', this.footer() ).on( 'keyup change', function () {
        if ( that.search() !== this.value ) {
            that
                .search(this.value)
                .draw();
        }
    } );
} );

So this is how you make server side configuration for jquery datables. But this is for the UI part. In next part, I would explain how could we make it easier for handling requests at server-side.

Leave a Reply

Your email address will not be published. Required fields are marked *

Privacy Preference Center

Necessary

Advertising

Analytics

Other