Multiple Column Search on Datatable in Lightning

This post will walk you through the implementation of Multiple Column Search on Datatable in Lightning.

As of now, there is no search functionality for Lightning Datatables so we have to implement custom search functionality. It is not a big deal to implement but it gets complicated if you want to filter records by matching the search term with multiple columns. It gets messier if you want to search with different search terms for different columns. For Example, if you want to search where the Name is “ABC” and the Country is “XYZ”.

To implement such functionality, we can make use of the jQuery Datatable library. We will tweak the search functionality to make it work on all the columns. As part of this example, we will put the search boxes for all the columns below the table header row and the table will be filtered based on the texts we enter in the search boxes. So let’s hop into the implementation.

Steps to Implement :

Follow the below steps to implement this. I will put all the code at the bottom of the post.

1. Create a Lightning component and include a jQuery Library JS, jQuery Datatable JS, and Datatable CSS.
2. Create a table in the Lightning component. Add the footer row for the table which will contain a row with text boxes for each column that will be used for filtering. We are not putting search boxes directly below the header row as it is easier to perform a search on the footer using jQuery.
3. Create an Apex class that will return the records to display in Datatable.
4. Once the table is loaded, move the table footer row which contains search boxes just below the header row of the table.
5. Create a listener for search boxes to filter the records dynamically.

If you want, you can make the columns dynamic so that columns will not be hardcoded. For the purpose of this blog, I am keeping things simple. You can check the implementation for that here: Dynamic List Component in Lightning.

Also Read:

• Drag and Drop in LWC (Lightning Web Component)
• Drag and Drop between LWC (Multiple)
• Custom Progress Indicator in Lightning

Code :

MultiColumnSearch.cmp

<aura:component implements="force:appHostable,flexipage:availableForAllPageTypes" 
                access="global" controller="MultiColumnSearchController">
    
	<!-- Including jQuery JS and jQuery datatable JS along with CSS-->
    <ltng:require styles="{! $Resource.	dataTableCss}"
                  scripts="{!join(',', $Resource.jQuery , $Resource.dataTable)}" />
    
    <!-- Attribures to save records and columns-->
    <aura:attribute name="recordList" type="Object"/>
    
    <!-- Init handler-->
    <aura:handler name="init" value="{!this}" action="{!c.doInit}"/>
    
    <br/><h1 class="slds-align_absolute-center">Account Records</h1><br/>
    
    <div id="idTable">
        <table id="tableId" class="slds-table slds-table_bordered slds-table_cell-buffer" cellspacing="0">
            <thead>
                <tr>
                    <th>Name</th>
                    <th>Account Number</th>
                    <th>Type</th>
                    <th>Phone</th>
                </tr>
            </thead>
            <tbody>
                <aura:iteration items="{!v.recordList}" var="objRecord">
                    <tr>
                        <td>{!objRecord.Name}</td>
                        <td>{!objRecord.AccountNumber}</td>
                        <td>{!objRecord.Type}</td>
                        <td>{!objRecord.Phone}</td>
                    </tr>
                </aura:iteration>  
            </tbody>
            
            <!-- This footer section will contain the search boxes which we will move at the top, below the headers.-->
            <!-- We are putting it as footer as we can easily access it while performing search using jQuery datatable methods. -->
            <tfoot>
                <tr>
                    <th><input type="text" placeholder="Search Name" /></th>
                    <th><input type="text" placeholder="Search Account Number" /></th>
                    <th><input type="text" placeholder="Search Type" /></th>
                    <th><input type="text" placeholder="Search Phone" /></th>
                </tr>
            </tfoot>
        </table>
	</div>
</aura:component>

MultiColumnSearchController.js

({
	doInit : function(component, event, helper) {
		helper.doInitHelper(component, event, helper);
	}
})

MultiColumnSearchHelper.js

({
	doInitHelper : function(component, event, helper) {
        
        // Calling apex class to query Accounts to populate recordList.
		var objAction = component.get("c.fetchAccounts");
    	objAction.setCallback(this, function(objResponse) {
            var objState = objResponse.getState();
            if (objState === "SUCCESS") {           
                var lstRecords = objResponse.getReturnValue();
                component.set('v.recordList', lstRecords);
                
                // Adding 500 milliseconds delay to avoid unexpected jQury runtime errors.
                setTimeout(function(){
                    try{
                        $('div.dataTables_filter input').addClass('slds-input');
                        $('div.dataTables_filter input').css("marginBottom", "10px");
                        
                        // Adding jQuery datatable features to table.
                        var objTable = $('#tableId').DataTable();
                     
                        // Adding ther listener to apply search filter.
                        objTable.columns().every( function () {
                            var objThat = this;
                     
                            $( 'input', this.footer() ).on( 'keyup change clear', function () {
                                if ( objThat.search() !== this.value ) {
                                    objThat.search( this.value ).draw();
                                }
                            } );
                        } );
                        
                        // Moving search boxed at the top below the headers.
                        $('#tableId tfoot tr').appendTo('#tableId thead');
                    }catch(error){
                        // Error Handling.
                    }
                }, 100);
                
            } else if (objState === "ERROR") {
                // Error Handling.
            }
        });
        $A.enqueueAction(objAction);
	}
})

MultiColumnSearch.css

/* Hiding the standard jQuery datatable search box as we are implementing custom one.*/
.THIS #tableId_filter{
    display: none;
}

MultiColumnSearchController.apxc

public class MultiColumnSearchController {
    
	@AuraEnabled
    public static list <Account> fetchAccounts() {
      return [SELECT Name, AccountNumber, Type, Phone FROM Account WHERE AccountNumber != NULL LIMIT 50];
    }
}

Multiple Column Search on DataTable in Lightning

This is how our implementation will look like:

You can download the Static Resources referred in above code from below link:
Multiple Column Search on Datatable in Lightning

If you don’t want to miss new post, please Subscribe here.

If you want to check more custom implementations in Lightning, you can check it here.