Filtering Data with DHTMLX Grid: Complex Filter and Performance

Introduction

DHTMLX library provides instruments to filter grid by columns value (https://docs.dhtmlx.com/grid__filtering.html). These instruments include tools, that implement "OR" and "AND" logic. However, sometimes, it is not enough. For example, there is a necessity to implement filters like: colA*colB > colC or (colA>10 OR colA< 0) AND colC > 1 or take into account row attributes, etc.

Another filtering issue is performance. One of the techniques of loading bulk amount of data to DHTMLX grid is smart rendering. Smart rendering can be coupled with dynamic loading to improve performance even more. However it is not always possible. In case of smart rendering with static loading, filtering of grid with thousands of rows can cause serious problems with performance.

In such a way, there are two filtering issues: implementation of complex filtering and improvement of filtering performance for grid that use smart rendering with static loading and I am going to give some tips to solve them.

Issue 1

Implementation of Complex Filter Based on DHTMLX Library Filtering Instruments

According to DHTMLX documentation, there is function to perform filtering by particular column (https://docs.dhtmlx.com/api__dhtmlxgrid_filterby.html). The second parameter can be both: a string and a function. Let's look at the case with function.

myGrid.filterBy(1,function(data){
    // true - show the related row, false - hide the related row
    return data.toString().indexOf("alf")!=-1;
});

Function, that is the second parameter and actually performs filtering, accepts "data" as a parameter - the column value. That is not enough to perform complex filtering because there is no information about row, therefore there is no way to get other row data (other column's value or row attributes).

Solution

We need to know the row identifier to implement desired filtering. And indeed, if we dig into DHTMLX library code, we came up the function that performs filtering that accepts two parameters: data and row identifier.

myGrid.filterBy(0,function(data, rowId){
    return customFiltering(rowId); //implement filtering
});

Row identifier passed as a parameter gives the opportunity to implement much wider range of filtering (first argument - column index can be anything).

Issue 2

Improvement of Filtering Performance for Grid with Smart Rendering and Static Loading

Smart rendering postpones rendering of rows till these rows become visible. And this improves loading performance of huge grids, because from 1000s of row grid, it renders only 30, that are visible on start. However, filtering usually takes into account columns or attribute values for a particular row. So the methods like getRowAttribute (https://docs.dhtmlx.com/api__dhtmlxgrid_getrowattribute.html) cellByIndex (https://docs.dhtmlx.com/api__dhtmlxgrid_cellbyindex.html) or similar are called to get row attribute or cell value. Calling such functions raise the tor rendering and the benefits of smart rendering are lost.

Solution

To keep benefits of smart rendering during filtration, locate the data for filtering outside the grid. For example, move row attribute from actual row attribute to JavaScript map. And when you need to analyze it, address the map instead of row attribute. This prevents rendering grid row during filtering. In other words, keep everything that you need for filtering outside of grid. Such approach will cause some memory overhead and maybe additional code to synchronize the grid and external structures that you use for filtering, but, at the same time, it can benefit in terms of performance.