Server side pagination/processing with Datatable js in Salesforce

In this article, I am going to tell you how to use Datatable js for Sever side processing in Salesforce. As we all know salesforce has 135kb of view state limit. So when we are using datatable js with client side processing with large number of data, always there is chance to hit view state limit. So we will use server side processing to avoid view state limit as we will keep only those record in client side those are shown to user currently. We will use standard controller and datatable js to implement server side processing. In order to do this, we need to create following pages and extension. We will use contact records for this example.

1. VF page to show contact details in table i.e. main page for user
2. VF page, which will work as helper page and every time it will be called to get data in json
3. Extension for helper page to process data in backend.

First lets create a page which will show a table with pagination, sorting, selectable number of entries and search box to serach on FirstName and LastName. we will use contact records. Lets name it as ContactDatatable.page

ContactDatatable.page

  <apex:page showheader="false" standardstylesheets="false">
  <link href="https://cdn.datatables.net/1.10.6/css/jquery.dataTables.css" rel="stylesheet"    type="text/css"></link> 
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
  <script src="//cdn.datatables.net/1.10.6/js/jquery.dataTables.min.js"></script>
 <table cellspacing="0" class="display" id="example" style="width: 100%px;">
        <thead>
          <tr>
              <th>First name</th>
              <th>Last name</th>
              <th>Birthdate</th>
              <th>Phone</th>
              <th>Email</th>
          </tr>
      </thead>
      <tfoot>
         <tr>
             <th>First name</th>
             <th>Last name</th>
             <th>Birthdate</th>
             <th>Phone</th>
             <th>Email</th>  
         </tr>
</tfoot>
    </table>
<script>
           $(document).ready(function() {
            $.fn.dataTableExt.sErrMode = 'console';   //block alert                                     
            $('#example').dataTable( {
                "processing": true,
                "serverSide": true,
                "ajax": "/apex/DataTableHelper",
                "columns": [
                    { "data": "FirstName" },
                    { "data": "LastName" },
                    { "data": "Birthdate" },
                    { "data": "Phone" },
                    { "data": "Email" }     
                ]
            } );
        } );
    </script>
</apex:page>

• In the above code, first css and js files are included for datatable js. you can find details about required files here.
• A table is crated with header and footer(with column header) without any content. we will fetch content dynamically. Note this page will not work yet as we need to create DatatableHelper and its extension.
• In the end of file data table js in intialized with server side processing. Find details of syntax here.

DataTableHelper.page

<apex:page contentType="text/plain" showHeader="false" sidebar="false" applyHtmlTag="false" controller="DataTableHelper" action="{!processData}">
  <apex:outputText value="{!dataTableJson }"></apex:outputText>
</apex:page>

• ContentType of this page "text/plain" as this page will return a json without any html tag.

public class DataTableHelper{

    Public Integer noOfRecords{get; set;}
    Public Integer size{get;set;}
    Public Integer start{get;set;}
    string queryString;
    public ApexPages.StandardSetController setCon {get;set;}

    public DataTableHelper(){
      
    }


    public string dataTableJson {get;set;}
    
    public void processData(){
        queryString = 'select FirstName,LastName,Birthdate from Contact';
        string searchKey = ApexPages.currentPage().getParameters().get('search[value]');
        if(searchKey != null && searchKey != '' && searchKey.trim().length() > 0){
            queryString += ' Where FirstName like \'%'+searchKey+'%\' or LastName like \'%'+searchKey+'%\''; 
        } 
        integer i = 0;
        String str = 'order[0][column]';
        //set order by clause , this code is assume that the sorting is on only one field but this can modified accordingly
        //also set direction from order[0][dir] parameter. note: we are taking 0th element as we have assume only one sorting
        queryString += ' order by ' + ApexPages.currentPage().getParameters().get('columns['+ApexPages.currentPage().getParameters().get(str) + '][data]') + ' ' + 
                                ApexPages.currentPage().getParameters().get('order[0][dir]');
        system.debug('+++query'+queryString );
        //get starting record number for current view, this parametter will be send by datatable js
        start= Integer.valueOf(ApexPages.currentPage().getParameters().get('start'));
        //current number of records per page, it is also in avilable in get request
        size = Integer.valueOf(ApexPages.currentPage().getParameters().get('length'));
        //intialize standard controller with query
        setCon = new ApexPages.StandardSetController(Database.getQueryLocator(queryString));
        setCon.setPageSize(size);
        noOfRecords= setCon.getResultSize();
        //set pagenumber
        setCon.setPageNumber((start/size)+1);
        List contactList = setCon.getRecords();
        //create wrapper
        DataTableWrapper datawrap = new DataTableWrapper(0,noOfRecords,noOfRecords,contactList );
        dataTableJson = JSON.serialize(datawrap);
        
    }
    public class DataTableWrapper{
        public Integer draw;
        public Integer recordsTotal;
        public Integer recordsFiltered;
        public List data;
        public DataTableWrapper(Integer draw,Integer recordsTotal,Integer recordsFiltered,list data){
            this.draw = draw;
            this.recordsTotal = recordsTotal;
            this.recordsFiltered = recordsFiltered ;
            this.data = data;
        }
        
    }
   
}

• All l these paramter will send by data table js while requesting for page. Details can be found here.
• Code is written to support sorting on the basis of only one column but can be modified to support multiple sorting with use of multiple field in order by clause.
• the search functionality is only enabled for FirstName and LastName but you can change it according to requirement. But ensure that your searching is on index field to avoid performance issue.
• in this code standard controller is used to fetch record because using SOQL, there will be a offset limit as currently Salesforce support maximum 2000 as offset value is 2000.

That's all. Hope this article would helpful for server side processing using Datatable js.