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




$(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.

 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
/*
* 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

Untuk pemesanan, hubungi kami melalui kontak yang tersedia berikut:

Chat WhatsApp Kirim SMS Telpon

Komentar (0)

Posting Komentar