Exploring on Salesforce: Keyword Search Series - Display data using Visualforce and Datatable using sever side ajax

Use Case

User wants to search on Case description using some keyword. The data should be displayed in datatable. An account can have more than 5000 records, even it reaches up to 9000 records.

Important points to consider

In most of the blog posts, I have seen that, data is pulled up on the screen load and then performs the keyword search using datatable search input box. That approach will not work over here since there is large volume of data and user doesn't want to see all the data on page load.
Since search needs to be performed on Case Description which is Large Text Area (32000), so SOQL WHERE clause is not applicable.

Approach and Solution

Here, we have to leverage ajax's server side call which takes following input, basically the URL which returns the "data.json". Refer Datatable - Server Side Processing

$(document).ready(function() {
    $('#example').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": "../server_side/scripts/server_processing.php"
    } );
} );

Secondly, we need to perform SOSL search on keywords.

I have taken the datatable server side approach from this excellent blog post Server side pagination/processing with Datatable js in Salesforce

Only thing, I have blended this with SOSL search here.

Main Visualforce

Here server side call is performed through this

"ajax": "{!$Page.AdvancedSearchDataTableHelperPage}?core.apexpages.devmode.url=1&id={!$CurrentPage.parameters.id}"

1:  <!--========================================================================================-->  
2:  <!--Name: AdvancedSearchCasePage                            -->  
3:  <!--========================================================================================-->  
4:  <!--=========================================================================================->  
5:  <!-- Purpose:                                        -->  
6:  <!-- This page is used to perform Advance Search on Cases related to particular Account,    -->   
7:  <!--========================================================================================-->  
8:  <!--========================================================================================-->  
9:  <apex:page>  
10:    <apex:form>  
11:      <!-- Jquery -->  
12:      <apex:includeScript value="//code.jquery.com/jquery-1.11.3.min.js" />  
13:      <apex:stylesheet value="//ajax.googleapis.com/ajax/libs/jqueryui/1.11.4/themes/smoothness/jquery-ui.css"/>  
14:      <!-- DataTable -->  
15:      <apex:includeScript value="//cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"/>  
16:      <apex:stylesheet value="//cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css"/>  
17:      <!-- Search Highlight -->  
18:      <apex:includeScript value="//bartaz.github.io/sandbox.js/jquery.highlight.js" />  
19:      <apex:includeScript value="//cdn.datatables.net/plug-ins/1.10.9/features/searchHighlight/dataTables.searchHighlight.min.js"/>  
20:      <apex:stylesheet value="//cdn.datatables.net/plug-ins/1.10.9/features/searchHighlight/dataTables.searchHighlight.css"/>  
21:      <style>  
22:        .filterMatches {  
23:          background-color: #BFFF00;  
24:        }  
25:        .tertiaryPalette {  
26:          color: #000 !important;  
27:        }  
28:        .dt-buttons {  
29:          margin-left: 10px;  
30:        }  
31:      </style>  
32:      <script type="text/javascript">  
33:        $(document).ready(function() {  
34:       //  alert('{!$CurrentPage.parameters.id}');  
35:          $.fn.dataTableExt.sErrMode = 'console';  //block alert   
36:          //this is for Case table  
37:          $('#table1').dataTable( {  
38:            searchHighlight: true,  
39:            //sets record lengths to show in picklist  
40:            aLengthMenu: [  
41:              [10, 25, 50, 100, 200, -1],  
42:              [10, 25, 50, 100, 200, "All"]  
43:            ],  
44:            "iDisplayLength": 10,  
45:            //adds copy, print buttons...  
46:            dom: 'lBrtip', //l=length, B=buttons, f=filter(search), r=processing, t=the table, I=table summary, p=page controls  
47:            buttons: [],            
48:            "processing": true,  
49:            "serverSide": true,  
50:            "ajax": "{!$Page.AdvancedSearchDataTableHelperPage}?core.apexpages.devmode.url=1&id={!$CurrentPage.parameters.id}",  
51:            "columns": [  
52:              { "data": "CaseNumber" },  
53:              { "data": "Subject" },  
54:              { "data": "Description" }  
55:            ]  
56:          } );  
57:        } );  
58:        $.fn.dataTableExt.oApi.fnFilterAll = function(oSettings, sInput,  
59:          iColumn, bRegex, bSmart) {  
60:          var settings = $.fn.dataTableSettings;  
61:          for (var i = 0; i < settings.length; i++) {  
62:            settings[i].oInstance.fnFilter(sInput, iColumn, bRegex,  
63:              bSmart);  
64:          }  
65:        };  
66:        $(document).ready(function() {  
67:          $('#table1').dataTable({  
68:            "bPaginate": false,  
69:          });  
70:          var oTable0 = $("#table1").dataTable();  
71:          $("#Search_All").keyup(function() {  
72:            // Filter on the column (the index) of this element  
73:            oTable0.fnFilterAll(this.value);  
74:          });  
75:        });      
76:      </script>  
77:      <apex:pageBlock id="header">  
78:      <apex:outputLabel value="Advanced Search of Cases by Account:" style="font-size:medium; font-weight:bold;" /> <br /><br />  
79:        <label>Search:</label>  
80:        <input type="text" id="Search_All" placeholder="Enter Search Keyword" />        
81:        <apex:commandLink action="{!URLFOR($Action.Account.View, $CurrentPage.parameters.id)}" value="Return"   
82:        styleClass="btn" style="padding: 4px; text-decoration: none;float: right;"/>  
83:      </apex:pageBlock>  
84:      <apex:pageBlock title="Cases" id="cases">  
85:        <table cellspacing="0" class="display" id="table1" style="width: 100%px;">  
86:          <thead>  
87:           <tr>  
88:             <th>Case</th>  
89:             <th>Subject</th>  
90:             <th>Description</th>  
91:           </tr>  
92:         </thead>  
93:        </table>  
94:      </apex:pageBlock>  
95:      <apex:commandLink action="{!URLFOR($Action.Account.View, $CurrentPage.parameters.id)}" value="Return"   
96:        styleClass="btn" style="padding: 4px; text-decoration: none;float: right;"/>  
97:    </apex:form>  
98:  </apex:page>

Ajax search Visualforce

This page returns actually renders JSON data upon calling processData through page's action.

1:  <apex:page id="AdvancedSearchDataTableHelperPage" contentType="application/x-JavaScript; charset=utf-8"   
2:       showHeader="false" sidebar="false" applyHtmlTag="false" controller="AdvancedSearchDataTableHelper" action="{!processData}">  
3:   {!dataTableJson }  
4:  </apex:page>

Ajax search Controller

Few points I have considered here:

It will consider at-least 2 digit input
Secondly in the search input appending '*'
Data set returned from SOSL directly assigning into StandardSetController's constructor.
Limiting records up to 2000 records otherwise it might give ViewState error or Heap Size error as Description is 32k characters.
Finally, creating JSON formatted data which datatable expects.

/*
 * Created By: Santanu Boral
 * Purpose: This class is used by AdvancedSearchDataTableHelper        
 * -------------------------------------------------------------------------- 
 */
public class AdvancedSearchDataTableHelper{
 
    Public Integer noOfRecords{get; set;}
    Public Integer size{get;set;}
    Public Integer start{get;set;}
    
    
    public String accountId {get;set;}
    
    public AdvancedSearchDataTableHelper(){
         
    }
 
    public string dataTableJson {get;set;}
    
    public void processData()
    {    
        accountId =ApexPages.CurrentPage().getparameters().get('id');
  
        String searchString = ApexPages.currentPage().getParameters().get('search[value]');       
        if(null!=searchString && searchString.length()>2)
        {
            if(!searchString.endsWith('*'))
            {
                searchString = searchString + '*';
            }
            //get starting record number for current view, this parametter will be send by datatable js
            start= Integer.valueOf(ApexPages.currentPage().getParameters().get('start'));
            //start= 0;//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
            
            ApexPages.StandardSetController setConCase = new ApexPages.StandardSetController([FIND :searchString
                                    IN ALL FIELDS RETURNING Case (id,CaseNumber,Subject, Description
                                    WHERE AccountId = :accountId
                                    )                                                         
                                    LIMIT 2000][0]);
            
            setConCase.setPageSize(size);
            noOfRecords= setConCase.getResultSize();
            system.debug('noOfRecords '+noOfRecords);
         
            setConCase.setPageNumber((start/size)+1);
            List<SObject> caseList = setConCase.getRecords();
            System.debug('caseList=' + caseList);
   
            //create wrapper
            DataTableWrapper datawrap = new DataTableWrapper(0,noOfRecords,noOfRecords,caseList);
            dataTableJson = JSON.serialize(datawrap);
        }
        else
        {
            DataTableWrapper datawrap = new DataTableWrapper(0,0,0,new List<SObject>());
            dataTableJson = JSON.serialize(datawrap);
        }
         
    }
    public class DataTableWrapper{
        public Integer draw;
        public Integer recordsTotal;
        public Integer recordsFiltered;
        public List<SObject> data;
        public DataTableWrapper(Integer draw,Integer recordsTotal,Integer recordsFiltered,list<SObject> data){
            this.draw = draw;
            this.recordsTotal = recordsTotal;
            this.recordsFiltered = recordsFiltered ;
            this.data = data;
        }
         
    }    
}

This visualforce page can be called from a Detail Page button. The code behind the button will be

/apex/AdvancedSearchCasePage?id=<accountId>

Results

Initially user will be given to this page.

User will provide at-least 3 digit input and then server side call will populate the data as follows

Conclusion

I have found this approach is really helpful in terms of performance and hence sharing with you.

Further experiment

In my next post, I will post searching on Case Comments where this approach doesn't work fully. Reason behind why, please check out my next post.

Keyword Search Series - Display paginated data using Visualforce and Datatable for CaseComments with server side search

Exploring on Salesforce

Pages

Sunday, March 11, 2018

Keyword Search Series - Display data using Visualforce and Datatable using sever side ajax