Create Filter with AND/OR Conditons

In addition to many other powerful features, Ag-Grid provides native filtering capabilities that are comprehensive and allow significant customization. However, sometimes it may seem that out-of-the-box, not all use cases are covered. 

For example, sometimes you have to use AND and OR conditions when combining filters across multiple columns, such as this one - [Year]=2004 OR [Country]='Netherlands'.

Even though this isn't provided by ag-Grid out-of-the-box, we can still easily implement it with a bit of code. We'll show you two ways of doing this depending on the type of filter you're using.

Immutable Data

Using immutable data, it is possible to achieve AND/OR conditions across multiple columns. This is because we are setting new data each time a filter is applied. getRowNodeId is vital in such examples as it allows the grid to maintain the data's integrity by keeping track of rows that are visible in on the screen. The key here is keeping an immutable source of data (what is originally loaded) and supplying the grid with new permutations as and when a filter needs to be applied. 

JS example: https://plnkr.co/edit/L0ekYx6oFxC58WcH 

Angular example: https://plnkr.co/edit/3E36sKal5sBIXH73 

The key code in both examples is below: 

function onRadioFilterChanged(newFilter) {
  let newData;
  switch (newFilter) {
    case 'everyone':
      newData = [...immutableStore];
      break;
    case 'excluding20to30': //OR CASE
      newData = immutableStore.filter((row) => row.age < 20 || row.age > 30);
      break;
    case 'between20and30': // AND CASE
      newData = immutableStore.filter((row) => row.age >= 20 && row.age <= 30);
      break;
    case 'netherlandsOr2004': // OR CASE ACROSS DIFFERENT COLUMNS
      newData = immutableStore.filter(
        (row) => row.year === 2004 || row.country === 'Netherlands'
      );
      break;
    default:
      console.warn('unknown case');
  }
  gridOptions.api.setRowData(newData);
}

As you can see, each time the radio buttons are interacted with, this function is invoked. With each case, the immutable store data is either copied or filtered to generate the required data and then set as row data via the grid's API. 

External Filters

We can implement any combination of AND & OR filtering using the isExternalFilterPresent & doesExternalFilterPass API members of ag-Grid.

For a full rundown of what this functionality is capable of, take a look at the documentation here:

https://www.ag-grid.com/javascript-grid-filter-external/ 

For the purposes of this article, I will be using the plunker from the above documentation page as a starting point. 

See this implemented with external filtering UI in this sample:

https://plnkr.co/edit/TtAwOt9v66FLcu9A?preview

In this sample, I have adapted the switch cases within doesEnternalFilterPass function to accept our new OR cases like this: 

function doesExternalFilterPass(node) {
  switch (ageType) {
    case 'excluding20to30': //OR CASE
      return node.data.age < 20 || node.data.age > 30;
    case 'between20and30': // AND CASE
      return node.data.age >= 20 && node.data.age <= 30;
    case 'netherlandsOr2004': // OR CASE ACROSS DIFFERENT COLUMNS
      return node.data.country == 'Netherlands' || node.data.year == 2004;
    default:
      return true;
  };
};

The first case shows how the OR case is simply applied on a single column condition. All we're telling ag-Grid is where to look for the data we want to filter (in the first case it is node.data.age) and what conditions we want it to meet. 

In the third case, we can see that the above logic can be applied across different columns, in this case, Country and Year. This case will filter for rows with Country="Netherlands" OR Year=2004.

Custom Filter Component

Ag-Grid provides a custom filter allowing you additional flexibility in behavior. You can find out more about this type of filter here:

https://www.ag-grid.com/javascript-grid-filter-custom/

We have implemented this same scenario by hosting the filtering UI in the column filter inside the grid using a custom filter component. Please see this here:

https://plnkr.co/edit/Swu99dJfhZBHOcY0?preview

I used the filter component example from our docs as a base to build from. Once again, I'm using a very simple method to apply my OR filter condition between two columns: 

YearFilter.prototype.doesFilterPass = function(params) {
    return (
        params.data.sport == 'Speed Skating' || params.data.country == 'Japan'
    );
};

In this case, you can see that even though the filter is accessible via the hamburger icon in the header of the Year column, the filtering condition can be set to include any column as once again we are telling the filter where to look for data (in params.data.sport or params.data.country). In this instance, we're looking for rows with either sport='Speed Skating' or country='Japan'. 

I hope you see how using these two API members you have full control of the filtering condition and can build complex filters including multiple columns and different levels of AND and OR conditions in. This is especially valuable when using external filters which can encode the frequently used conditions your users have to manually enter across multiple columns.

We hope you can use this advice and deliver UI that puts the filtering conditions users need within easy reach and makes filtering a breeze!