Advanced Filtering in AG Grid using OR-Filtering Conditions

Author Credit Ahmed Gadir

In this post we will demonstrate how you can apply OR-Filtering conditions in AG Grid while also maintaining the option to filter by AG Grid's default AND-filter conditions. We will do this by adding a Custom Tool Panel component to allow the end-user to enter OR-filtering conditions and applying the OR-filtering conditions before binding the data to the grid, thus enabling the grid to apply its default AND-filtering later on.

See this implemented in the sample below:

• How it works
• AND-Filtering vs. OR-Filtering
• Accessing Row Data via React Context
• Custom Filter Panel for OR-filter conditions
• Binding OR-Filtered data to AG Grid

How it works

AG Grid allows you to display tool panels alongside the grid - by default you have a columns and a filters tool panel. You can add your own tool panelsfor any custom functionality. We've added two custom tool panels to let you set the AND and OR-filtering conditions separately, as shown in the screenshot below.

The OR-filtering tool panel will apply OR-Filtering conditions to the data and bind the result to the AG Grid row data property. The AND-filtering tool panel will then apply AND-Filtering conditions to AG Grid row data, displaying the matching records in the grid.

AND-Filtering vs. OR-Filtering

The default filtering behaviour of AG Grid applies AND-filtering, which means that rows must pass every column filter condition to get filtered through. In contrast, OR-filtering conditions allow rows that pass any filter condition to be filtered through.

To demonstrate this let's take the following dataset:

[
    { athlete: “Robyn Ah Mow-Santos”, country: “United States”, sport: “Volleyball” }, 
    { athlete: “Anderson”, country: “Brazil”, sport: “Volleyball” },
    { athlete: “Lloy Ball”, country: “United States”, sport: “Volleyball” },
    { athlete: “Yu Seung-Min”, country: “South Korea”, sport: “Table Tennis” }
]

Here we have four rows, each with a key-value pair for the properties: athlete, country and sport. If we apply the following AND filter to the dataset:

country="United States" && sport="Volleyball"

We get the result:

[
    { athlete: “Robyn Ah Mow-Santos”, country: “United States”, sport: “Volleyball” }, 
    { athlete: “Lloy Ball”, country: “United States”, sport: “Volleyball” },
]

Note how all results pass both of the defined filter conditions.

However if we apply the OR-filter to the dataset:

country="United States" || sport="Volleyball"

We get a different result:

[
    { athlete: “Robyn Ah Mow-Santos”, country: “United States”, sport: “Volleyball” }, 
    { athlete: “Anderson”, country: “Brazil”, sport: “Volleyball” },
    { athlete: “Lloy Ball”, country: “United States”, sport: “Volleyball” },
]

The result contains both rows from the previous filter, but also an additional record (athlete="Anderson"). Notice how this record matches only one of the two specified filter conditions (sport="Volleyball").

Accessing Row Data via React Context

Let's now look into how the 2 types (AND and OR) of filtering conditions are applied to the record set. Our demo application works by keeping track of 2 separate sets of row data:

• rowData - the original, unfiltered row data
• orFilteredData - rows matching the OR-filtering conditions

These are both kept in a React Context which is accessible from anywhere within the application defined as shown below:

// src/DataContext.js

const DataContext = createContext({
  rowData: [],
  orFilteredRowData: [],
  setOrFilteredRowData: () => { }
});

Custom Filter Panel for OR-filtering conditions

As illustrated before, our demo application uses two filter tool panels, one for each column filtering mode:

• The provided agFiltersToolPanel componentwhich allows AND-Filtering
• OrFilterPanel - A custom tool panel component which allows OR-filtering

See these two tool panels defined in the code segment below:

<AgGridReact
    // ...
    frameworkComponents={{ orFilterPanel: OrFilterPanel }}
    sideBar={{
        toolPanels: [
            {
                id: 'or-filtering',
                labelDefault: 'OR-filtering',
                labelKey: 'or-filtering',
                iconKey: 'filter',
                toolPanel: 'orFilterPanel',
                toolPanelParams: {
                    columnDefs: columnDefs,
                }
            },
            {
                id: 'and-filtering',
                labelDefault: 'AND-filtering',
                labelKey: 'and-filtering',
                iconKey: 'filter',
                toolPanel: 'agFiltersToolPanel',
            },

        ],
        defaultToolPanel: 'or-filtering',
    }}

Let's now focus on the custom tool panel which handles OR-filter conditions. On initializing, the OrFilterPanel creates a state variable availableFilterOptions, where every key is an AG Grid column and every value is a JavaScript Array containing all the unique values in DataContext.rowData for that key.

Each key-value pair in availableFilterOptions is then bound to a ReactSelectMe Dropdown element. This useful third-party package virtualises the option elements it shows, allowing us to render efficiently dropdowns with long lists of options to represent all the distinct cell values in the column.

We keep track of the selected dropdown options in the state variable selectedFilterOptions. This state variable contains the filtering conditions to be used in the OR-filtering algorithm. In the screenshot below, the selection in the panel on the left is represented by the state variable value on the right.

Every time the selected filter options are updated we apply the OR-filtering algorithm to the unfiltered data and update dataContext.orFilteredRowData with the matching rows as shown in the code below:

// src/Components/OrFilterPanel.js

 if (noSelectedFilterOptions) {
    updatedFilteredRowData = dataContext.rowData.map(row => ({ ...row }));
} else {
    updatedFilteredRowData = dataContext.rowData
        .map(row => ({ ...row }))
        .filter(row =>
            Object.entries(row).some(([field, value]) =>
                selectedFilterOptions[field] && selectedFilterOptions[field].some(selectedFilters => selectedFilters === value)
            )
        );
}

// update context
dataContext.setOrFilteredRowData(updatedFilteredRowData);

The orFilteredData is then bound to our AG Grid instance's rowData property and displayed.

Binding OR-Filtered data to AG Grid

One of the key aspects of this approach is that AG Grid row data is not directly bound to the unfiltered original set of rows. Instead, the AG Grid instance binds its rowData property to DataContext.orFilteredRowData immutably as shown below:

// src/Components/MyGrid.js

<AgGridReact
    rowData={dataContext.orFilteredRowData}
    immutableData={true}
    getRowNodeId={data => data.id}
    ...

This means that the OR-filtering is applied externally from the grid and is transparent to it. This is why grid receives data that may already be OR-filtered and can apply its built-in AND-filtering conditions on top the OR-filtered data if needed.

You can see the sample in action, applying OR-filtering together with AND-filtering in the GIF below using the following filter query:

(country="Keyna || sport="Beach Volleyball") && year=2012

Summary

I hope this article helps illustrate how to configure your AG Grid to deliver both OR-filtering in addition to the default AND-filtering behaviour. This approach will allow your users to filter large datasets with ease and quickly find the data they're looking for.

If you would like to try out AG Grid check out our getting started guides (JS / React / Angular / Vue)

Happy coding!