JavaScript Data GridSSRM Filtering

Version 36.2.0

Enterprise

This section covers Filtering using the Server-Side Row Model (SSRM).

Enabling Filtering Copy Link

Filtering is enabled in the grid via the filter column definition attribute.

const gridOptions = {
    columnDefs: [
        // sets the 'text' filter
        { field: 'country', filter: 'agTextColumnFilter' },

        // use the default 'set' filter
        { field: 'year', filter: true },

        // no filter (unspecified)
        { field: 'sport' },
    ],

    // other grid options ...
}

For more details on filtering configurations see the section on Column Filtering.

Server-side Filtering Copy Link

The actual filtering of rows is performed on the server when using the Server-Side Row Model. When a filter is applied in the grid a request is made for more rows via getRows(params) on the Server-Side Datasource. The supplied params includes a request containing filter metadata contained in the filterModel property.

The request object sent to the server contains filter metadata in the filterModel property, an example is shown below:

// Example request with filter info
{
    filterModel: {
        athlete: {
            filterType: 'text',
            type: 'contains',
            filter: 'fred'
        },
        year: {
            filterType: 'number',
            type: 'greaterThan',
            filter: 2005,
            filterTo: null
        }
    },

    // other properties
}

Notice in the snippet above the filterModel object contains a 'text' and 'number' filter. This filter metadata is used by the server to perform the filtering.

For more details on properties and values used in these filters see the sections on Text Filter Model and Number Filter Model.

The example below demonstrates filtering using Simple Column Filters, note the following:

The Athlete column has a 'text' filter defined using filter: 'agTextColumnFilter'.
The Year column has a 'number' filter defined using filter: 'agNumberColumnFilter'.
The medals columns have a 'number' filter defined using filter: 'agNumberColumnFilter' on the 'number' column type.
The server uses the metadata contained in the filterModel to filter the rows.
Open the browser's dev console to view the filterModel supplied in the request to the datasource.

main.ts

import {
  GridApi,
  GridOptions,
  IServerSideDatasource,
  ModuleRegistry,
  NumberFilterModule,
  TextFilterModule,
  createGrid,
  enableDevValidations,
} from "ag-grid-community";
import {
  ColumnMenuModule,
  ContextMenuModule,
  ServerSideRowModelModule,
} from "ag-grid-enterprise";
import { FakeServer } from "./fakeServer";
import { IOlympicData } from "./interfaces";

if (process.env.NODE_ENV !== "production") {
  // Enable extended validations only for development
  enableDevValidations();
}

ModuleRegistry.registerModules([
  ColumnMenuModule,
  ContextMenuModule,
  ServerSideRowModelModule,
  TextFilterModule,
  NumberFilterModule,
]);

let gridApi: GridApi<IOlympicData>;
const gridOptions: GridOptions<IOlympicData> = {
  columnDefs: [
    {
      field: "athlete",
      filter: "agTextColumnFilter",
      minWidth: 220,
    },
    {
      field: "year",
      filter: "agNumberColumnFilter",
      filterParams: {
        buttons: ["reset"],
        debounceMs: 1000,
        maxNumConditions: 1,
      },
    },
    { field: "gold", type: "number" },
    { field: "silver", type: "number" },
    { field: "bronze", type: "number" },
  ],
  defaultColDef: {
    flex: 1,
    minWidth: 100,
    suppressHeaderMenuButton: true,
    suppressHeaderContextMenu: true,
  },
  columnTypes: {
    number: { filter: "agNumberColumnFilter" },
  },
  // use the server-side row model
  rowModelType: "serverSide",
};

function getServerSideDatasource(server: any): IServerSideDatasource {
  return {
    getRows: (params) => {
      console.log("[Datasource] - rows requested by grid: ", params.request);

      // get data for request from our fake server
      const response = server.getData(params.request);

      // simulating real server call with a 500ms delay
      setTimeout(() => {
        if (response.success) {
          // supply rows for requested block to grid
          params.success({
            rowData: response.rows,
            rowCount: response.lastRow,
          });
        } else {
          params.fail();
        }
      }, 500);
    },
  };
}

const gridDiv = document.querySelector<HTMLElement>("#myGrid")!;
gridApi = createGrid(gridDiv, gridOptions);

fetch("https://www.ag-grid.com/example-assets/olympic-winners.json")
  .then((response) => response.json())
  .then(function (data) {
    // setup the fake server with entire dataset
    const fakeServer = new FakeServer(data);

    // create datasource with a reference to the fake server
    const datasource = getServerSideDatasource(fakeServer);

    // register the datasource with the grid
    gridApi!.setGridOption("serverSideDatasource", datasource);
  });

fakeServer.ts

// This fake server uses http://alasql.org/ to mimic how a real server
// might generate sql queries from the Server-Side Row Model request.
// To keep things simple it does the bare minimum to support the example.
export function FakeServer(allData) {
    alasql.options.cache = false;

    return {
        getData: function (request) {
            const results = executeQuery(request);

            return {
                success: true,
                rows: results,
                lastRow: getLastRowIndex(request),
            };
        },
    };

    function executeQuery(request) {
        const sql = buildSql(request);

        console.log('[FakeServer] - about to execute query:', sql);

        return alasql(sql, [allData]);
    }

    function buildSql(request) {
        return 'SELECT * FROM ?' + whereSql(request) + orderBySql(request) + limitSql(request);
    }

    function whereSql(request) {
        const whereParts = [];
        const filterModel = request.filterModel;

        if (filterModel) {
            Object.keys(filterModel).forEach(function (key) {
                const item = filterModel[key];

                switch (item.filterType) {
                    case 'text':
                        whereParts.push(createFilterSql(textFilterMapper, key, item));
                        break;
                    case 'number':
                        whereParts.push(createFilterSql(numberFilterMapper, key, item));
                        break;
                    default:
                        console.log('unknown filter type: ' + item.filterType);
                        break;
                }
            });
        }

        if (whereParts.length > 0) {
            return ' WHERE ' + whereParts.join(' AND ');
        }

        return '';
    }

    function createFilterSql(mapper, key, item) {
        if (item.operator) {
            const conditions = item.conditions.map((condition) => mapper(key, condition));

            return '(' + conditions.join(' ' + item.operator + ' ') + ')';
        }

        return mapper(key, item);
    }

    function textFilterMapper(key, item) {
        switch (item.type) {
            case 'equals':
                return key + " = '" + item.filter + "'";
            case 'notEqual':
                return key + " != '" + item.filter + "'";
            case 'contains':
                return key + " LIKE '%" + item.filter + "%'";
            case 'notContains':
                return key + " NOT LIKE '%" + item.filter + "%'";
            case 'startsWith':
                return key + " LIKE '" + item.filter + "%'";
            case 'endsWith':
                return key + " LIKE '%" + item.filter + "'";
            case 'blank':
                return key + ' IS NULL or ' + key + " = ''";
            case 'notBlank':
                return key + ' IS NOT NULL and ' + key + " != ''";
            default:
                console.log('unknown text filter type: ' + item.type);
        }
    }

    function numberFilterMapper(key, item) {
        switch (item.type) {
            case 'equals':
                return key + ' = ' + item.filter;
            case 'notEqual':
                return key + ' != ' + item.filter;
            case 'greaterThan':
                return key + ' > ' + item.filter;
            case 'greaterThanOrEqual':
                return key + ' >= ' + item.filter;
            case 'lessThan':
                return key + ' < ' + item.filter;
            case 'lessThanOrEqual':
                return key + ' <= ' + item.filter;
            case 'inRange':
                return '(' + key + ' >= ' + item.filter + ' and ' + key + ' <= ' + item.filterTo + ')';
            case 'blank':
                return key + ' IS NULL';
            case 'notBlank':
                return key + ' IS NOT NULL';
            default:
                console.log('unknown number filter type: ' + item.type);
        }
    }

    function orderBySql(request) {
        const sortModel = request.sortModel;

        if (sortModel.length === 0) return '';

        const sorts = sortModel.map(function (s) {
            return s.colId + ' ' + s.sort.toUpperCase();
        });

        return ' ORDER BY ' + sorts.join(', ');
    }

    function limitSql(request) {
        if (request.endRow == undefined || request.startRow == undefined) {
            return '';
        }
        const blockSize = request.endRow - request.startRow;

        return ' LIMIT ' + blockSize + ' OFFSET ' + request.startRow;
    }

    function getLastRowIndex(request) {
        return executeQuery({ ...request, startRow: undefined, endRow: undefined }).length;
    }
}

index.html

<div id="myGrid" style="height: 100%"></div>

interfaces.ts


export interface IOlympicData {
    athlete: string,
    age: number,
    country: string,
    year: number,
    date: string,
    sport: string,
    gold: number,
    silver: number,
    bronze: number,
    total: number
}

Date Filters Copy Link

For more details on Date Filter model properties and available options see Date Filter and Filter Model.

Preset Date Range Filters Copy Link

When using a Date Filter with built-in date ranges (for example, Today or Last 7 Days), the Grid State model and the SSRM request model are identical. Both use the preset type and leave dateFrom and dateTo as undefined. Your server (or custom filtering code) must interpret the preset and apply the equivalent date range logic using its own time zone and locale rules.

ISimpleFilterModelPresetType

Preset range type (for example, today or last7Days).

undefined

Preset range does not provide an explicit from date.

undefined

Preset range does not provide an explicit to date.

'date'

Filter type is always 'date'

Set Filtering Copy Link

Filtering using the Set Filter has a few differences to filtering with Simple Filters.

Set Filter Model Copy Link

Entries in the filterModel have a different format to the Simple Filters. This filter model is what gets passed as part of the request to the server when using Server-side Filtering. The following shows an example of a Set Filter where two items are selected:

// IServerSideGetRowsRequest
{
    filterModel: {
        country: {
            filterType: 'set',
            values: ['Australia', 'Belgium']
        }
    },

    // other properties
}

Set Filter Values Copy Link

When using the Set Filter with the SSRM it is necessary to supply the values as the grid does not have all rows loaded. This can be done either synchronously or asynchronously using the values filter param as shown below:

const gridOptions = {
    columnDefs: [
        // colDef with Set Filter values supplied synchronously
        {
            field: 'country',
            filter: 'agSetColumnFilter',
            filterParams: {
                values: ['Australia', 'China', 'Sweden']
            }
        },
        // colDef with Set Filter values supplied asynchronously
        {
            field: 'country',
            filter: 'agSetColumnFilter',
            filterParams: {
                values: params => {
                    // simulating async delay
                    setTimeout(() => params.success(['Australia', 'China', 'Sweden']), 500);
                }
            }
        }
    ],

    // other grid options ...
}

For more details on setting values, see Supplying Filter Values. Once you have supplied values to the Set Filter, they will not change unless you ask for them to be refreshed. See Refreshing Values for more information.

The example below demonstrates Server-side Filtering using the Set Filter. Note the following:

The Country column has a Set Filter defined using filter: 'agSetColumnFilter'.
The Sport column has a Multi Filter defined using filter: 'agMultiColumnFilter', it combines a Set Filter and a Text Filter.
Set Filter values are fetched asynchronously and supplied via the params.success(values) callback.
The filter for the Country column is using complex objects. The country name is shown in the Filter List, but the filterModel (and request) use the country code.
The filter for the Sport column only shows the values which are available for the selected countries. When the filter for the Country column is changed, the values for the Sport filter are updated.
The server uses the metadata contained in the filterModel to filter the rows.
Open the browser's dev console to view the filterModel supplied in the request to the datasource.

main.ts

import {
  ColDef,
  GridApi,
  GridOptions,
  IMultiFilter,
  IServerSideDatasource,
  KeyCreatorParams,
  ModuleRegistry,
  SetFilterHandler,
  SetFilterUi,
  SetFilterValuesFuncParams,
  TextFilterModule,
  ValueFormatterParams,
  createGrid,
  enableDevValidations,
} from "ag-grid-community";
import {
  ColumnMenuModule,
  ContextMenuModule,
  MultiFilterModule,
  ServerSideRowModelModule,
  SetFilterModule,
} from "ag-grid-enterprise";
import { FakeServer } from "./fakeServer";
import { IOlympicData } from "./interfaces";

if (process.env.NODE_ENV !== "production") {
  // Enable extended validations only for development
  enableDevValidations();
}

ModuleRegistry.registerModules([
  ColumnMenuModule,
  ContextMenuModule,
  ServerSideRowModelModule,
  SetFilterModule,
  MultiFilterModule,
  TextFilterModule,
]);

const columnDefs: ColDef[] = [
  {
    field: "country",
    filter: "agSetColumnFilter",
    valueFormatter: countryValueFormatter,
    filterParams: {
      values: getCountryValuesAsync,
      keyCreator: countryCodeKeyCreator,
      valueFormatter: countryValueFormatter,
      comparator: countryComparator,
      suppressClearModelOnRefreshValues: true,
      buttons: ["apply"],
    },
  },
  {
    field: "sport",
    filter: "agMultiColumnFilter",
    filterParams: {
      filters: [
        {
          filter: "agTextColumnFilter",
          filterParams: {
            defaultOption: "startsWith",
          },
        },
        {
          filter: "agSetColumnFilter",
          filterParams: {
            values: getSportValuesAsync,
            suppressClearModelOnRefreshValues: true,
          },
        },
      ],
    },
    menuTabs: ["filterMenuTab"],
  },
  { field: "athlete" },
];

function countryCodeKeyCreator(params: KeyCreatorParams): string {
  return params.value.code;
}

function countryValueFormatter(params: ValueFormatterParams): string {
  return params.value.name;
}

function countryComparator(
  a: { name: string; code: string },
  b: { name: string; code: string },
): number {
  // for complex objects, need to provide a comparator to choose what to sort by
  if (a.name < b.name) {
    return -1;
  } else if (a.name > b.name) {
    return 1;
  }
  return 0;
}

let gridApi: GridApi<IOlympicData>;

const gridOptions: GridOptions<IOlympicData> = {
  columnDefs: columnDefs,
  defaultColDef: {
    flex: 1,
    minWidth: 150,
    suppressHeaderMenuButton: true,
    suppressHeaderContextMenu: true,
  },
  // use the server-side row model
  rowModelType: "serverSide",

  // fetch 100 rows at a time
  cacheBlockSize: 100,

  // only keep 10 blocks of rows
  maxBlocksInCache: 10,

  onFilterChanged: onFilterChanged,
};

let fakeServer: any;
let selectedCountries: string[] | null = null;
let textFilterStored: string[] | null = null;

function onFilterChanged() {
  const countryFilterModel = gridApi!.getFilterModel()["country"];
  const sportFilterModel = gridApi!.getFilterModel()["sport"];
  const selected = countryFilterModel && countryFilterModel.values;
  const textFilter = sportFilterModel?.filterModels[0]
    ? sportFilterModel.filterModels[0]
    : null;

  if (
    !areEqual(selectedCountries, selected) ||
    !areEqual(textFilterStored, textFilter)
  ) {
    selectedCountries = selected;
    textFilterStored = textFilter;

    console.log("Refreshing sports filter");
    // By default, the Multi Filter does not use a filter handler, so retrieve via `getColumnFilterInstance`.
    // If using `enableFilterHandlers = true`, the Multi Filter handler can be retrieved via `getColumnFilterHandler`.
    gridApi.getColumnFilterInstance<IMultiFilter>("sport").then((filter) => {
      filter!
        .getChildFilterInstance<SetFilterUi>(1)!
        .getFilterHandler()
        .refreshFilterValues();
    });
    gridApi
      .getColumnFilterHandler<SetFilterHandler>("country")!
      .refreshFilterValues();
  }
}

export function areEqual(
  a: readonly any[] | null | undefined,
  b: readonly any[] | null | undefined,
): boolean {
  if (a === b) {
    return true; // Same instance, no need to compare
  }
  if (!a || !b) {
    return a == null && b == null;
  }
  const len = a.length;
  if (len !== b.length) {
    return false;
  }
  for (let i = 0; i < len; i++) {
    if (a[i] !== b[i]) {
      return false;
    }
  }
  return true;
}

function getCountryValuesAsync(params: SetFilterValuesFuncParams) {
  const sportFilterModel = params.api.getFilterModel()["sport"];
  const countries = fakeServer.getCountries(sportFilterModel);

  // simulating real server call with a 500ms delay
  setTimeout(() => {
    params.success(countries);
  }, 500);
}

function getSportValuesAsync(params: SetFilterValuesFuncParams) {
  const sportFilterModel = params.api.getFilterModel()["sport"];
  const sports = fakeServer.getSports(selectedCountries, sportFilterModel);

  // simulating real server call with a 500ms delay
  setTimeout(() => {
    params.success(sports);
  }, 500);
}

function getServerSideDatasource(server: any): IServerSideDatasource {
  return {
    getRows: (params) => {
      console.log("[Datasource] - rows requested by grid: ", params.request);

      // get data for request from our fake server
      const response = server.getData(params.request);

      // simulating real server call with a 500ms delay
      setTimeout(() => {
        if (response.success) {
          // supply rows for requested block to grid
          params.success({
            rowData: response.rows,
            rowCount: response.lastRow,
          });
        } else {
          params.fail();
        }
      }, 500);
    },
  };
}

const gridDiv = document.querySelector<HTMLElement>("#myGrid")!;
gridApi = createGrid(gridDiv, gridOptions);

fetch("https://www.ag-grid.com/example-assets/olympic-winners.json")
  .then((response) => response.json())
  .then(function (data) {
    // we don't have unique codes in our dataset, so generate unique ones
    const namesToCodes: Map<string, string> = new Map();
    const codesToNames: Map<string, string> = new Map();
    data.forEach((row: any) => {
      row.countryName = row.country;
      if (namesToCodes.has(row.countryName)) {
        row.countryCode = namesToCodes.get(row.countryName);
      } else {
        row.countryCode = row.country.substring(0, 2).toUpperCase();
        if (codesToNames.has(row.countryCode)) {
          let num = 0;
          do {
            row.countryCode = `${row.countryCode[0]}${num++}`;
          } while (codesToNames.has(row.countryCode));
        }
        codesToNames.set(row.countryCode, row.countryName);
        namesToCodes.set(row.countryName, row.countryCode);
      }
      delete row.country;
    });
    // setup the fake server with entire dataset
    fakeServer = new FakeServer(data);

    // create datasource with a reference to the fake server
    const datasource = getServerSideDatasource(fakeServer);

    // register the datasource with the grid
    gridApi!.setGridOption("serverSideDatasource", datasource);
  });

fakeServer.ts

// This fake server uses http://alasql.org/ to mimic how a real server
// might generate sql queries from the Server-Side Row Model request.
// To keep things simple it does the bare minimum to support the example.
export function FakeServer(allData) {
    alasql.options.cache = false;

    return {
        getData: function (request) {
            const results = executeQuery(request);
            results.forEach((row) => {
                row.country = {
                    code: row.countryCode,
                    name: row.countryName,
                };
                delete row.countryCode;
                delete row.countryName;
            });

            return {
                success: true,
                rows: results,
                lastRow: getLastRowIndex(request),
            };
        },
        getCountries: function (sportFilter) {
            const textFilter = sportFilter?.filterModels[0]
                ? ' WHERE ' + textFilterMapper('sport', sportFilter.filterModels[0])
                : '';
            const sql = 'SELECT DISTINCT countryCode, countryName FROM ? ' + textFilter + ' ORDER BY countryName ASC';

            return alasql(sql, [allData]).map((row) => ({
                code: row.countryCode,
                name: row.countryName,
            }));
        },
        getSports: function (countries, sportFilter) {
            console.log('Returning sports for ' + (countries ? countries.join(', ') : 'all countries'));

            const where = countries ? " WHERE countryCode IN ('" + countries.join("', '") + "')" : '';
            const operator = countries ? ' AND ' : ' WHERE ';
            const textFilter = sportFilter?.filterModels[0]
                ? operator + textFilterMapper('sport', sportFilter.filterModels[0])
                : '';
            const sql = 'SELECT DISTINCT sport FROM ? ' + where + textFilter + ' ORDER BY sport ASC';

            return alasql(sql, [allData]).map(function (x) {
                return x.sport;
            });
        },
    };

    function executeQuery(request) {
        const sql = buildSql(request);

        console.log('[FakeServer] - about to execute query:', sql);

        return alasql(sql, [allData]);
    }

    function buildSql(request) {
        return 'SELECT * FROM ?' + whereSql(request) + orderBySql(request) + limitSql(request);
    }

    function mapColumnKey(columnKey) {
        return columnKey === 'country' ? 'countryCode' : columnKey;
    }

    function textFilterMapper(key, item) {
        switch (item.type) {
            case 'equals':
                return key + " = '" + item.filter + "'";
            case 'notEqual':
                return key + "' != '" + item.filter + "'";
            case 'contains':
                return key + " LIKE '%" + item.filter + "%'";
            case 'notContains':
                return key + " NOT LIKE '%" + item.filter + "%'";
            case 'startsWith':
                return key + " LIKE '" + item.filter + "%'";
            case 'endsWith':
                return key + " LIKE '%" + item.filter + "'";
            default:
                console.log('unknown text filter type: ' + item.type);
        }
    }

    function createFilterSql(mapper, key, item) {
        if (item.operator) {
            const conditions = item.conditions.map((condition) => mapper(key, condition));

            return '(' + conditions.join(' ' + item.operator + ' ') + ')';
        }

        return mapper(key, item);
    }

    function whereSql(request) {
        const whereParts = [];
        const filterModel = request.filterModel;

        if (filterModel) {
            Object.keys(filterModel).forEach(function (columnKey) {
                const filter = filterModel[columnKey];

                if (filter.filterType === 'set') {
                    whereParts.push(mapColumnKey(columnKey) + " IN ('" + filter.values.join("', '") + "')");
                    return;
                }

                if (filter.filterType === 'text') {
                    whereParts.push(createFilterSql(textFilterMapper, columnKey, filter));
                    return;
                }

                if (filter.filterType === 'multi') {
                    Object.keys(filter.filterModels).forEach(function (fm) {
                        if (filter.filterModels[fm]) {
                            const model = filter.filterModels[fm];
                            switch (model.filterType) {
                                case 'text':
                                    whereParts.push(createFilterSql(textFilterMapper, columnKey, model));
                                    break;
                                case 'set':
                                    whereParts.push(columnKey + " IN ('" + model.values.join("', '") + "')");
                                    break;
                                default:
                                    console.log('unknown filter type: ' + model.filterType, model);
                                    break;
                            }
                        }
                    });
                    return;
                }

                console.log('unsupported filter type: ' + filter.filterType);
            });
        }

        if (whereParts.length > 0) {
            return ' WHERE ' + whereParts.join(' AND ');
        }

        return '';
    }

    function orderBySql(request) {
        const sortModel = request.sortModel;

        if (sortModel.length === 0) return '';

        const sorts = sortModel.map(function (s) {
            return mapColumnKey(s.colId) + ' ' + s.sort.toUpperCase();
        });

        return ' ORDER BY ' + sorts.join(', ');
    }

    function limitSql(request) {
        if (request.endRow == undefined || request.startRow == undefined) {
            return '';
        }
        const blockSize = request.endRow - request.startRow;

        return ' LIMIT ' + blockSize + ' OFFSET ' + request.startRow;
    }

    function getLastRowIndex(request) {
        return executeQuery({ ...request, startRow: undefined, endRow: undefined }).length;
    }
}

index.html

<div id="myGrid" style="height: 100%"></div>

interfaces.ts


export interface IOlympicData {
    athlete: string,
    age: number,
    country: string,
    year: number,
    date: string,
    sport: string,
    gold: number,
    silver: number,
    bronze: number,
    total: number
}

Batching Filter Requests Copy Link

The New Filters Tool Panel allows for filter changes to be batched together before a new getRows request is sent to the datasource.

This is possible by configuring the New Filters Tool Panel to Use Buttons. Each filter can then be edited in the tool panel, and one request will be sent when the global apply button is clicked.

main.ts

import {
  FilterWrapperParams,
  GridApi,
  GridOptions,
  IServerSideDatasource,
  ModuleRegistry,
  NumberFilterModule,
  TextFilterModule,
  createGrid,
  enableDevValidations,
} from "ag-grid-community";
import {
  ColumnMenuModule,
  ContextMenuModule,
  NewFiltersToolPanelModule,
  ServerSideRowModelModule,
  SideBarModule,
} from "ag-grid-enterprise";
import { FakeServer } from "./fakeServer";
import { IOlympicData } from "./interfaces";

if (process.env.NODE_ENV !== "production") {
  // Enable extended validations only for development
  enableDevValidations();
}

ModuleRegistry.registerModules([
  ColumnMenuModule,
  ContextMenuModule,
  ServerSideRowModelModule,
  TextFilterModule,
  NumberFilterModule,
  SideBarModule,
  NewFiltersToolPanelModule,
]);

let gridApi: GridApi<IOlympicData>;
const gridOptions: GridOptions<IOlympicData> = {
  columnDefs: [
    {
      field: "athlete",
      cellDataType: "text",
      minWidth: 220,
    },
    { field: "year", cellDataType: "number" },
    { field: "gold", cellDataType: "number" },
    { field: "silver", cellDataType: "number" },
    { field: "bronze", cellDataType: "number" },
  ],
  defaultColDef: {
    flex: 1,
    minWidth: 100,
    filter: true,
    filterParams: {
      buttons: ["apply"], // set all filters to use buttons
    } as FilterWrapperParams,
    suppressHeaderMenuButton: true,
    suppressHeaderContextMenu: true,
  },
  rowModelType: "serverSide",
  enableFilterHandlers: true,
  suppressSetFilterByDefault: true,
  sideBar: {
    toolPanels: [
      {
        id: "filters-new",
        labelDefault: "Filters",
        labelKey: "filters",
        iconKey: "filter",
        toolPanel: "agNewFiltersToolPanel",
        toolPanelParams: {
          buttons: ["reset", "apply"],
        },
      },
    ],
    defaultToolPanel: "filters-new",
  },
};

function getServerSideDatasource(server: any): IServerSideDatasource {
  return {
    getRows: (params) => {
      console.log("[Datasource] - rows requested by grid: ", params.request);

      // get data for request from our fake server
      const response = server.getData(params.request);

      // simulating real server call with a 500ms delay
      setTimeout(() => {
        if (response.success) {
          // supply rows for requested block to grid
          params.success({
            rowData: response.rows,
            rowCount: response.lastRow,
          });
        } else {
          params.fail();
        }
      }, 500);
    },
  };
}

const gridDiv = document.querySelector<HTMLElement>("#myGrid")!;
gridApi = createGrid(gridDiv, gridOptions);

fetch("https://www.ag-grid.com/example-assets/olympic-winners.json")
  .then((response) => response.json())
  .then(function (data) {
    // setup the fake server with entire dataset
    const fakeServer = new FakeServer(data);

    // create datasource with a reference to the fake server
    const datasource = getServerSideDatasource(fakeServer);

    // register the datasource with the grid
    gridApi!.setGridOption("serverSideDatasource", datasource);
  });

fakeServer.ts

// This fake server uses http://alasql.org/ to mimic how a real server
// might generate sql queries from the Server-Side Row Model request.
// To keep things simple it does the bare minimum to support the example.
export function FakeServer(allData) {
    alasql.options.cache = false;

    return {
        getData: function (request) {
            const results = executeQuery(request);

            return {
                success: true,
                rows: results,
                lastRow: getLastRowIndex(request),
            };
        },
    };

    function executeQuery(request) {
        const sql = buildSql(request);

        console.log('[FakeServer] - about to execute query:', sql);

        return alasql(sql, [allData]);
    }

    function buildSql(request) {
        return 'SELECT * FROM ?' + whereSql(request) + orderBySql(request) + limitSql(request);
    }

    function whereSql(request) {
        const whereParts = [];
        const filterModel = request.filterModel;

        if (filterModel) {
            Object.keys(filterModel).forEach(function (key) {
                const item = filterModel[key];

                switch (item.filterType) {
                    case 'text':
                        whereParts.push(createFilterSql(textFilterMapper, key, item));
                        break;
                    case 'number':
                        whereParts.push(createFilterSql(numberFilterMapper, key, item));
                        break;
                    default:
                        console.log('unknown filter type: ' + item.filterType);
                        break;
                }
            });
        }

        if (whereParts.length > 0) {
            return ' WHERE ' + whereParts.join(' AND ');
        }

        return '';
    }

    function createFilterSql(mapper, key, item) {
        if (item.operator) {
            const conditions = item.conditions.map((condition) => mapper(key, condition));

            return '(' + conditions.join(' ' + item.operator + ' ') + ')';
        }

        return mapper(key, item);
    }

    function textFilterMapper(key, item) {
        switch (item.type) {
            case 'equals':
                return key + " = '" + item.filter + "'";
            case 'notEqual':
                return key + " != '" + item.filter + "'";
            case 'contains':
                return key + " LIKE '%" + item.filter + "%'";
            case 'notContains':
                return key + " NOT LIKE '%" + item.filter + "%'";
            case 'startsWith':
                return key + " LIKE '" + item.filter + "%'";
            case 'endsWith':
                return key + " LIKE '%" + item.filter + "'";
            case 'blank':
                return key + ' IS NULL or ' + key + " = ''";
            case 'notBlank':
                return key + ' IS NOT NULL and ' + key + " != ''";
            default:
                console.log('unknown text filter type: ' + item.type);
        }
    }

    function numberFilterMapper(key, item) {
        switch (item.type) {
            case 'equals':
                return key + ' = ' + item.filter;
            case 'notEqual':
                return key + ' != ' + item.filter;
            case 'greaterThan':
                return key + ' > ' + item.filter;
            case 'greaterThanOrEqual':
                return key + ' >= ' + item.filter;
            case 'lessThan':
                return key + ' < ' + item.filter;
            case 'lessThanOrEqual':
                return key + ' <= ' + item.filter;
            case 'inRange':
                return '(' + key + ' >= ' + item.filter + ' and ' + key + ' <= ' + item.filterTo + ')';
            case 'blank':
                return key + ' IS NULL';
            case 'notBlank':
                return key + ' IS NOT NULL';
            default:
                console.log('unknown number filter type: ' + item.type);
        }
    }

    function orderBySql(request) {
        const sortModel = request.sortModel;

        if (sortModel.length === 0) return '';

        const sorts = sortModel.map(function (s) {
            return s.colId + ' ' + s.sort.toUpperCase();
        });

        return ' ORDER BY ' + sorts.join(', ');
    }

    function limitSql(request) {
        if (request.endRow == undefined || request.startRow == undefined) {
            return '';
        }
        const blockSize = request.endRow - request.startRow;

        return ' LIMIT ' + blockSize + ' OFFSET ' + request.startRow;
    }

    function getLastRowIndex(request) {
        return executeQuery({ ...request, startRow: undefined, endRow: undefined }).length;
    }
}

index.html

<div id="myGrid" style="height: 100%"></div>

interfaces.ts


export interface IOlympicData {
    athlete: string,
    age: number,
    country: string,
    year: number,
    date: string,
    sport: string,
    gold: number,
    silver: number,
    bronze: number,
    total: number
}

Advanced Filter Copy Link

In addition to Column Filters, the Advanced Filter can also be used with the Server-Side Row Model. In this case, the filterModel in the request will be an Advanced Filter Model of type AdvancedFilterModel | null.

Set the appropriate Cell Data Type on each column to display the correct filter options, otherwise only 'text' options will be displayed. A column with a correctly configured Set Filter gains the is any of and is none of options.

In the example below, note the following:

enableAdvancedFilter = true, and the request's filterModel is an Advanced Filter Model.
cellDataType is set so Athlete shows the text options and the other columns the number options.
Athlete has a Set Filter, so the is any of and is none of options are available.

main.ts

import {
  GridApi,
  GridOptions,
  IServerSideDatasource,
  ModuleRegistry,
  NumberFilterModule,
  SetFilterValuesFuncParams,
  TextFilterModule,
  createGrid,
  enableDevValidations,
} from "ag-grid-community";
import {
  AdvancedFilterModule,
  ColumnMenuModule,
  ContextMenuModule,
  ServerSideRowModelModule,
  SetFilterModule,
} from "ag-grid-enterprise";
import { FakeServer } from "./fakeServer";
import { IOlympicData } from "./interfaces";

if (process.env.NODE_ENV !== "production") {
  // Enable extended validations only for development
  enableDevValidations();
}

ModuleRegistry.registerModules([
  TextFilterModule,
  NumberFilterModule,
  SetFilterModule,
  AdvancedFilterModule,
  ColumnMenuModule,
  ContextMenuModule,
  ServerSideRowModelModule,
]);

let fakeServer: any;

// The grid holds no rows to derive the list from, so the values are fetched from the server.
function getAthleteValuesAsync(params: SetFilterValuesFuncParams) {
  // simulating real server call with a 500ms delay
  setTimeout(() => {
    const athletes = fakeServer.getValues("athlete");
    params.success(athletes);
  }, 500);
}

let gridApi: GridApi<IOlympicData>;
const gridOptions: GridOptions<IOlympicData> = {
  columnDefs: [
    {
      field: "athlete",
      cellDataType: "text",
      minWidth: 220,
      filter: "agSetColumnFilter",
      filterParams: {
        values: getAthleteValuesAsync,
      },
    },
    {
      field: "year",
      cellDataType: "number",
    },
    {
      field: "gold",
      cellDataType: "number",
    },
    {
      field: "silver",
      cellDataType: "number",
    },
    {
      field: "bronze",
      cellDataType: "number",
    },
  ],
  defaultColDef: {
    flex: 1,
    minWidth: 100,
    filter: true,
    suppressHeaderMenuButton: true,
    suppressHeaderContextMenu: true,
  },
  // use the server-side row model
  rowModelType: "serverSide",

  enableAdvancedFilter: true,
};

function getServerSideDatasource(server: any): IServerSideDatasource {
  return {
    getRows: (params) => {
      console.log("[Datasource] - rows requested by grid: ", params.request);

      // get data for request from our fake server
      const response = server.getData(params.request);

      // simulating real server call with a 500ms delay
      setTimeout(() => {
        if (response.success) {
          // supply rows for requested block to grid
          params.success({
            rowData: response.rows,
            rowCount: response.lastRow,
          });
        } else {
          params.fail();
        }
      }, 500);
    },
  };
}

const gridDiv = document.querySelector<HTMLElement>("#myGrid")!;
gridApi = createGrid(gridDiv, gridOptions);

fetch("https://www.ag-grid.com/example-assets/olympic-winners.json")
  .then((response) => response.json())
  .then(function (data) {
    // setup the fake server with entire dataset
    fakeServer = new FakeServer(data);

    // create datasource with a reference to the fake server
    const datasource = getServerSideDatasource(fakeServer);

    // register the datasource with the grid
    gridApi!.setGridOption("serverSideDatasource", datasource);
  });

fakeServer.ts

// This fake server uses http://alasql.org/ to mimic how a real server
// might generate sql queries from the Server-Side Row Model request.
// To keep things simple it does the bare minimum to support the example.
export function FakeServer(allData) {
    alasql.options.cache = false;

    return {
        getValues: function (field) {
            const rows = alasql('SELECT DISTINCT ' + field + ' FROM ? ORDER BY ' + field, [allData]);

            return rows.map((row) => String(row[field]));
        },

        getData: function (request) {
            const results = executeQuery(request);

            return {
                success: true,
                rows: results,
                lastRow: getLastRowIndex(request),
            };
        },
    };

    function executeQuery(request) {
        const sql = buildSql(request);

        console.log('[FakeServer] - about to execute query:', sql);

        return alasql(sql, [allData]);
    }

    function buildSql(request) {
        return 'SELECT * FROM ?' + whereSql(request) + orderBySql(request) + limitSql(request);
    }

    function whereSql(request) {
        const filterModel = request.filterModel;

        if (filterModel) {
            return ' WHERE ' + createFilterSql(filterModel);
        }

        return '';
    }

    function createFilterSql(model) {
        if (model.filterType === 'join') {
            return '(' + model.conditions.map((condition) => createFilterSql(condition)).join(` ${model.type} `) + ')';
        } else {
            if (model.filterType === 'text') {
                return textFilterMapper(model.colId, model);
            } else if (model.filterType === 'number') {
                return numberFilterMapper(model.colId, model);
            } else if (model.filterType === 'set') {
                return setFilterMapper(model.colId, model);
            } else {
                console.log('filter type not implemented: ' + model.filterType);
                return ' 1 = 1 ';
            }
        }
    }

    function textFilterMapper(key, item) {
        switch (item.type) {
            case 'equals':
                return key + " = '" + item.filter + "'";
            case 'notEqual':
                return key + " != '" + item.filter + "'";
            case 'contains':
                return key + " LIKE '%" + item.filter + "%'";
            case 'notContains':
                return key + " NOT LIKE '%" + item.filter + "%'";
            case 'startsWith':
                return key + " LIKE '" + item.filter + "%'";
            case 'endsWith':
                return key + " LIKE '%" + item.filter + "'";
            case 'blank':
                return key + ' IS NULL or ' + key + " = ''";
            case 'notBlank':
                return key + ' IS NOT NULL and ' + key + " != ''";
            default:
                console.log('unknown text filter type: ' + item.type);
        }
    }

    // `is any of` / `is none of` send the Set Filter keys the written values resolved to, which are strings.
    // A blank is sent as a `null` key, so blank cells and the key are both folded to '' before comparing.
    function setFilterMapper(key, item) {
        const values = item.values.map((value) => "'" + (value ?? '').replace(/'/g, "''") + "'").join(', ');
        const operator = item.type === 'isNoneOf' ? ' NOT IN (' : ' IN (';

        return 'COALESCE(CAST(' + key + " AS STRING), '')" + operator + values + ')';
    }

    function numberFilterMapper(key, item) {
        switch (item.type) {
            case 'equals':
                return key + ' = ' + item.filter;
            case 'notEqual':
                return key + ' != ' + item.filter;
            case 'greaterThan':
                return key + ' > ' + item.filter;
            case 'greaterThanOrEqual':
                return key + ' >= ' + item.filter;
            case 'lessThan':
                return key + ' < ' + item.filter;
            case 'lessThanOrEqual':
                return key + ' <= ' + item.filter;
            case 'inRange':
                return '(' + key + ' >= ' + item.filter + ' and ' + key + ' <= ' + item.filterTo + ')';
            case 'blank':
                return key + ' IS NULL';
            case 'notBlank':
                return key + ' IS NOT NULL';
            default:
                console.log('unknown number filter type: ' + item.type);
        }
    }

    function orderBySql(request) {
        const sortModel = request.sortModel;

        if (sortModel.length === 0) return '';

        const sorts = sortModel.map(function (s) {
            return s.colId + ' ' + s.sort.toUpperCase();
        });

        return ' ORDER BY ' + sorts.join(', ');
    }

    function limitSql(request) {
        if (request.endRow == undefined || request.startRow == undefined) {
            return '';
        }
        const blockSize = request.endRow - request.startRow;

        return ' LIMIT ' + blockSize + ' OFFSET ' + request.startRow;
    }

    function getLastRowIndex(request) {
        return executeQuery({ ...request, startRow: undefined, endRow: undefined }).length;
    }
}

index.html

<div id="myGrid" style="height: 100%"></div>

interfaces.ts


export interface IOlympicData {
    athlete: string,
    age: number,
    country: string,
    year: number,
    date: string,
    sport: string,
    gold: number,
    silver: number,
    bronze: number,
    total: number
}

No Matching Rows Overlay Copy Link

When a filter model is applied and the data source returns no rows then the no-matching-rows overlay will automatically be displayed in the grid. See Provided Overlays for more details and how to suppress the overlay.

Getting Started

AI Features

Layout & Styling

Charting

Core Features

Advanced Features

JavaScript Data GridSSRM Filtering

Enabling Filtering Copy Link

Server-side Filtering Copy Link

Date Filters Copy Link

Preset Date Range Filters Copy Link

Set Filtering Copy Link

Set Filter Model Copy Link

Set Filter Values Copy Link

Batching Filter Requests Copy Link

Advanced Filter Copy Link

No Matching Rows Overlay Copy Link