Tag Archives: Sorting

Salesforce Lightning Custom Datatable Pagination & Sorting

Apex Class:

public class AccountController {
    
    @AuraEnabled//Get Account Records
    public static String getAccountList(Integer pageSize, Integer pageNumber, String sortingField, Boolean isSortAsc){
        
        String jsonDT = '';
        //Offset for SOQL
        Integer offset = (pageNumber - 1) * pageSize;
        
        //Total Records
        Integer totalRecords = [SELECT COUNT() FROM Account];
        Integer recordEnd = pageSize * pageNumber;
        
        String sortBy = isSortAsc ? 'ASC' : 'DESC';
        
        String query = 'SELECT Id, Name, AccountNumber, Industry, Phone FROM Account ORDER BY ';
        query += sortingField + ' ' + sortBy;
        query += ' LIMIT :pageSize OFFSET :offset';
        
        AccountDTWrapper objDT =  new AccountDTWrapper();  
        objDT.pageSize = pageSize;
        objDT.pageNumber = pageNumber;
        objDT.recordStart = offset + 1;
        objDT.recordEnd = totalRecords >= recordEnd ? recordEnd : totalRecords;
        objDT.totalRecords = totalRecords;
        objDT.accounts = Database.query(query);
        jsonDT = JSON.serialize(objDT);
        return jsonDT;
    }

    public class AccountDTWrapper {
        public Integer pageSize {get;set;}
        public Integer pageNumber {get;set;}
        public Integer totalRecords {get;set;}
        public Integer recordStart {get;set;}
        public Integer recordEnd {get;set;}
        public List<Account> accounts {get;set;}
    }
}

Lightning Component:

<aura:component implements="force:appHostable,flexipage:availableForAllPageTypes" access="global" controller="AccountController">
    
    <!--Declare Attributes-->
    <aura:attribute name="accounts" type="List"/>
    <aura:attribute name="pageNumber" type="integer" default="1"/>
    <aura:attribute name="pageSize" type="integer" default="10"/>
    <aura:attribute name="totalPages" type="integer" default="0"/>
    <aura:attribute name="totalRecords" type="integer" default="0"/>
    <aura:attribute name="recordStart" type="integer" default="0"/>
    <aura:attribute name="recordEnd" type="integer" default="0"/>
    
    <aura:attribute name="isSortByName" type="Boolean" default="false"/>
    <aura:attribute name="isSortByAccNo" type="Boolean" default="false"/>
    <aura:attribute name="isSortByIndustry" type="Boolean" default="false"/>
    <aura:attribute name="isSortByPhone" type="Boolean" default="false"/>
    <aura:attribute name="isSortAsc" type="Boolean" default="true"/>
    <aura:attribute name="selectedSortingField" type="String" default="Name"/>
    
    <!--Declare Handlers-->
    <aura:handler name="init" action="{!c.doInit}" value="{!this}"/>
    
    <!--Component Start-->
    <div class="slds-m-around_xx-large">
        <lightning:card>
            <aura:set attribute="title">
                Accounts
            </aura:set>
            <aura:set attribute="footer">
                <div class="slds-align_absolute-center"> 
                    <div class="slds-p-right_xx-small">
                        
                        <lightning:button label="Prev"
                                          onclick="{!c.handlePrev}"
                                          disabled="{! v.pageNumber == 1}"
                                          variant="brand"
                                          iconName="utility:back"
                                          name="prev"/>
                    </div>
                    <span class="slds-badge slds-badge_lightest">
                        {!v.recordStart}-{!v.recordEnd} of {!v.totalRecords} | Page {!v.pageNumber} of {!v.totalPages}
                    </span>
                    <div class="slds-p-left_xx-small">
                        <lightning:button label="Next"
                                          disabled="{!v.pageNumber == v.totalPages}"
                                          onclick="{!c.handleNext}"
                                          variant="brand"
                                          iconName="utility:forward"
                                          iconPosition="right"
                                          name="next"/>
                    </div>
                </div>  
                
            </aura:set>
            <table class="slds-table slds-table_cell-buffer slds-table_bordered">
                <thead>
                    <tr class="slds-line-height_reset slds-text-title_caps">
                        <th style="width:30%" data-record="Name" class="slds-is-resizable" scope="col" onclick="{!c.handleSorting}">
                            <a href="javascript:void(0);" class="slds-th__action slds-text-link--reset">
                                <span class="slds-assistive-text">Sort</span>
                                <span class="slds-truncate" title="Name">Name</span>
                                <aura:if isTrue="{!v.isSortByName}">
                                    <aura:if isTrue="{!v.isSortAsc}">
                                        &#9650;
                                        <aura:set attribute="else">
                                            &#9660;
                                        </aura:set>
                                    </aura:if> 
                                </aura:if>
                            </a>
                        </th>
                        <th style="width:20%"  data-record="AccountNumber" class="slds-is-resizable" scope="col" onclick="{!c.handleSorting}">
                            <a href="javascript:void(0);" class="slds-th__action slds-text-link--reset">
                                <span class="slds-assistive-text">Sort</span>
                                <span class="slds-truncate" title="Account Number">Account Number</span>
                                <aura:if isTrue="{!v.isSortByAccNo}">
                                    <aura:if isTrue="{!v.isSortAsc}">
                                        &#9650;
                                        <aura:set attribute="else">
                                            &#9660;
                                        </aura:set>
                                    </aura:if> 
                                </aura:if>
                            </a>
                        </th>
                        <th style="width:20%"  data-record="Industry" class="slds-is-resizable" scope="col" onclick="{!c.handleSorting}">
                            <a href="javascript:void(0);" class="slds-th__action slds-text-link--reset">
                                <span class="slds-assistive-text">Sort</span>
                                <span class="slds-truncate" title="Industry">Industry</span>
                                <aura:if isTrue="{!v.isSortByIndustry}">
                                    <aura:if isTrue="{!v.isSortAsc}">
                                        &#9650;
                                        <aura:set attribute="else">
                                            &#9660;
                                        </aura:set>
                                    </aura:if> 
                                </aura:if>
                            </a>
                        </th>
                        <th style="width:20%"  data-record="Phone" class="slds-is-resizable" scope="col" onclick="{!c.handleSorting}">
                            <a href="javascript:void(0);" class="slds-th__action slds-text-link--reset">
                                <span class="slds-assistive-text">Sort</span>
                                <span class="slds-truncate" title="Phone">Phone</span>
                                <aura:if isTrue="{!v.isSortByPhone}">
                                    <aura:if isTrue="{!v.isSortAsc}">
                                        &#9650;
                                        <aura:set attribute="else">
                                            &#9660;
                                        </aura:set>
                                    </aura:if> 
                                </aura:if>
                            </a>
                        </th>
                        <th scope="col" style="width:10%">
                            
                        </th>
                    </tr>
                </thead>
                <aura:if isTrue="{!not(empty(v.accounts))}">
                    <tbody>
                        <aura:iteration items="{!v.accounts}" var="acc">
                            <tr class="slds-hint-parent">
                                
                                <th data-label="Name" scope="row">
                                    <div class="slds-truncate" title="{!acc.Name}">
                                        {!acc.Name}
                                    </div>
                                </th>
                                <td data-label="Account Number">
                                    <div class="slds-truncate" title="{!acc.AccountNumber}">{!acc.AccountNumber}</div>
                                </td>
                                <td data-label="Industry">
                                    <div class="slds-truncate" title="{!acc.Industry}">{!acc.Industry}</div>
                                </td>
                                <td data-label="Phone">
                                    <div class="slds-truncate" title="{!acc.Phone}">{!acc.Phone}</div>
                                </td>
                                <td>
                                    <div class="slds-align_absolute-center">
                                        <lightning:buttonMenu alternativeText="Show menu" menuAlignment="auto" onselect="{!c.handleRowAction}" value="{!acc.Id}">
                                            <lightning:menuItem value="edit" label="Edit" iconName="utility:edit" title="Edit" />
                                            <lightning:menuItem value="view" label="View" iconName="utility:description" title="View" />
                                        </lightning:buttonMenu>
                                    </div>
                                </td>
                            </tr>
                        </aura:iteration>
                    </tbody>
                </aura:if>
            </table>
            <aura:if isTrue="{!empty(v.accounts)}">
                <div class="slds-align_absolute-center">
                    No records found
                </div>
            </aura:if>
        </lightning:card>
    </div>
    <!--Component End-->
    
</aura:component>

Lightning JS Controller:

({
    doInit : function(component, event, helper) {        
        helper.getAccounts(component, helper);
    },
    
    handleNext : function(component, event, helper) { 
        var pageNumber = component.get("v.pageNumber");
        component.set("v.pageNumber", pageNumber+1);
        helper.getAccounts(component, helper);
    },
    
    handlePrev : function(component, event, helper) {        
        var pageNumber = component.get("v.pageNumber");
        component.set("v.pageNumber", pageNumber-1);
        helper.getAccounts(component, helper);
    },

    handleRowAction: function (component, event, helper) {
        var selectedAction = event.detail.menuItem.get("v.value");
        var selectedAccountId = event.getSource().get("v.value");
        switch (selectedAction) {
            case 'edit':
                helper.editRecord(component, event, selectedAccountId);
                break;
            case 'view':
                helper.viewRecord(component, event, selectedAccountId);
                break;
        }
    },
    
    handleSorting: function (component, event, helper) {
        var selectedItem = event.currentTarget;
        var selectedField = selectedItem.dataset.record;
        
        component.set("v.isSortByName", false);
        component.set("v.isSortByAccNo", false);
        component.set("v.isSortByIndustry", false);
        component.set("v.isSortByPhone", false);
        component.set("v.selectedSortingField", selectedField);
        
        if(selectedField == 'Name'){
            component.set("v.isSortByName", true);
        }else if(selectedField == 'AccountNumber'){
            component.set("v.isSortByAccNo", true);
        }else if(selectedField == 'Industry'){
            component.set("v.isSortByIndustry", true);
        }else if(selectedField == 'Phone'){
            component.set("v.isSortByPhone", true);
        }
        helper.sortColumnData(component, event);
    },
})

Lightning JS Helper:

({
    getAccounts: function(component, event) {
        var action = component.get("c.getAccountList");
        action.setParams({
            "pageSize": component.get("v.pageSize"),
            "pageNumber": component.get("v.pageNumber"),
            "sortingField": component.get("v.selectedSortingField"),
            "isSortAsc": component.get("v.isSortAsc")
        });
        action.setCallback(this, function(response) {
            var state = response.getState();
            if (component.isValid() && state === "SUCCESS"){
                var result = response.getReturnValue();
                if(result){
                    var resultData = JSON.parse(result);
                    var pageSize = component.get("v.pageSize");
                    component.set("v.accounts", resultData.accounts);
                    component.set("v.pageNumber", resultData.pageNumber);
                    component.set("v.totalRecords", resultData.totalRecords);
                    component.set("v.recordStart", resultData.recordStart);
                    component.set("v.recordEnd", resultData.recordEnd);
                    component.set("v.totalPages", Math.ceil(resultData.totalRecords / pageSize));
                }
            }
        });
        $A.enqueueAction(action);
    },
    
    viewRecord : function(component, event, selectedAccountId) {
        var navEvt = $A.get("e.force:navigateToSObject");
        navEvt.setParams({
            "recordId": selectedAccountId,
            "slideDevName": "detail"
        });
        navEvt.fire();
    },
    
    editRecord : function(component, event, selectedAccountId) {
        var editRecordEvent = $A.get("e.force:editRecord");
        editRecordEvent.setParams({
            "recordId": selectedAccountId
        });
        editRecordEvent.fire();
    },
    
    sortColumnData: function(component, event) {
        var accountList = component.get("v.accounts");
        var isSortAsc = component.get("v.isSortAsc");
        var sortingField = component.get("v.selectedSortingField");
        accountList.sort(function(a, b){
            var s1 = a[sortingField] == b[sortingField];
            var s2 = (!a[sortingField] && b[sortingField]) || (a[sortingField] < b[sortingField]);
            return s1? 0: (isSortAsc?-1:1)*(s2?1:-1);
        });
        component.set("v.accounts", accountList);
        component.set("v.isSortAsc", !isSortAsc);
    },
})