React Data GridSSRM Filtering

Version 36.1.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 [columnDefs, setColumnDefs] = useState([
    // sets the 'text' filter
    { field: 'country', filter: 'agTextColumnFilter' },

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

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

<AgGridReact columnDefs={columnDefs} />

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.

index.tsx

("use client");

import React, {
  useCallback,
  useMemo,
  useRef,
  useState,
  StrictMode,
} from "react";
import { createRoot } from "react-dom/client";
import { AgGridReact, AgGridProvider } from "ag-grid-react";
import {
  ColDef,
  ColGroupDef,
  ColTypeDefs,
  GridApi,
  GridOptions,
  GridReadyEvent,
  IServerSideDatasource,
  ModuleRegistry,
  NumberFilterModule,
  RowModelType,
  TextFilterModule,
  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") {
  enableDevValidations();
}

const modules = [
  ColumnMenuModule,
  ContextMenuModule,
  ServerSideRowModelModule,
  TextFilterModule,
  NumberFilterModule,
];

const getServerSideDatasource: (server: any) => IServerSideDatasource = (
  server: any,
) => {
  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 GridExample = () => {
  const containerStyle = useMemo(() => ({ width: "100%", height: "100%" }), []);
  const gridStyle = useMemo(() => ({ height: "100%", width: "100%" }), []);

  const [columnDefs, setColumnDefs] = useState<ColDef[]>([
    {
      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" },
  ]);
  const defaultColDef = useMemo<ColDef>(() => {
    return {
      flex: 1,
      minWidth: 100,
      suppressHeaderMenuButton: true,
      suppressHeaderContextMenu: true,
    };
  }, []);
  const columnTypes = useMemo<ColTypeDefs>(() => {
    return {
      number: { filter: "agNumberColumnFilter" },
    };
  }, []);

  const onGridReady = useCallback((params: GridReadyEvent) => {
    fetch("https://www.ag-grid.com/example-assets/olympic-winners.json")
      .then((resp) => resp.json())
      .then((data: IOlympicData[]) => {
        // 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
        params.api!.setGridOption("serverSideDatasource", datasource);
      });
  }, []);

  return (
    <AgGridProvider modules={modules}>
      <div style={containerStyle}>
        <div style={gridStyle}>
          <AgGridReact<IOlympicData>
            columnDefs={columnDefs}
            defaultColDef={defaultColDef}
            columnTypes={columnTypes}
            rowModelType={"serverSide"}
            onGridReady={onGridReady}
          />
        </div>
      </div>
    </AgGridProvider>
  );
};

const root = createRoot(document.getElementById("root")!);
root.render(
  <StrictMode>
    <GridExample />
  </StrictMode>,
);

fakeServer.tsx

// 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;
    }
}

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 [columnDefs, setColumnDefs] = useState([
    // 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);
            }
        }
    }
]);

<AgGridReact columnDefs={columnDefs} />

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.

index.tsx

("use client");

import React, {
  useCallback,
  useMemo,
  useRef,
  useState,
  StrictMode,
} from "react";
import { createRoot } from "react-dom/client";
import { AgGridReact, AgGridProvider } from "ag-grid-react";
import {
  ColDef,
  ColGroupDef,
  GridApi,
  GridOptions,
  GridReadyEvent,
  IMultiFilter,
  IServerSideDatasource,
  KeyCreatorParams,
  ModuleRegistry,
  RowModelType,
  SetFilterHandler,
  SetFilterUi,
  SetFilterValuesFuncParams,
  TextFilterModule,
  ValueFormatterParams,
  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") {
  enableDevValidations();
}

const modules = [
  ColumnMenuModule,
  ContextMenuModule,
  ServerSideRowModelModule,
  SetFilterModule,
  MultiFilterModule,
  TextFilterModule,
];

const countryCodeKeyCreator: (params: KeyCreatorParams) => string = (
  params: KeyCreatorParams,
) => {
  return params.value.code;
};

const countryValueFormatter: (params: ValueFormatterParams) => string = (
  params: ValueFormatterParams,
) => {
  return params.value.name;
};

const countryComparator: (
  a: {
    name: string;
    code: string;
  },
  b: {
    name: string;
    code: string;
  },
) => number = (
  a: {
    name: string;
    code: string;
  },
  b: {
    name: string;
    code: string;
  },
) => {
  // 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 fakeServer: any;

let selectedCountries: string[] | null = null;

let textFilterStored: string[] | null = null;

export const areEqual: (
  a: readonly any[] | null | undefined,
  b: readonly any[] | null | undefined,
) => boolean = (
  a: readonly any[] | null | undefined,
  b: readonly any[] | null | undefined,
) => {
  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);
}

const getServerSideDatasource: (server: any) => IServerSideDatasource = (
  server: any,
) => {
  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 GridExample = () => {
  const gridRef = useRef<AgGridReact<IOlympicData>>(null);
  const containerStyle = useMemo(() => ({ width: "100%", height: "100%" }), []);
  const gridStyle = useMemo(() => ({ height: "100%", width: "100%" }), []);

  const [columnDefs, setColumnDefs] = useState<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" },
  ]);
  const defaultColDef = useMemo<ColDef>(() => {
    return {
      flex: 1,
      minWidth: 150,
      suppressHeaderMenuButton: true,
      suppressHeaderContextMenu: true,
    };
  }, []);

  const onGridReady = useCallback((params: GridReadyEvent) => {
    fetch("https://www.ag-grid.com/example-assets/olympic-winners.json")
      .then((resp) => resp.json())
      .then((data: IOlympicData[]) => {
        // 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
        params.api!.setGridOption("serverSideDatasource", datasource);
      });
  }, []);

  const onFilterChanged = useCallback(() => {
    const countryFilterModel = gridRef.current!.api.getFilterModel()["country"];
    const sportFilterModel = gridRef.current!.api.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`.
      gridRef
        .current!.api.getColumnFilterInstance<IMultiFilter>("sport")
        .then((filter) => {
          filter!
            .getChildFilterInstance<SetFilterUi>(1)!
            .getFilterHandler()
            .refreshFilterValues();
        });
      gridRef
        .current!.api.getColumnFilterHandler<SetFilterHandler>("country")!
        .refreshFilterValues();
    }
  }, [textFilterStored]);

  return (
    <AgGridProvider modules={modules}>
      <div style={containerStyle}>
        <div style={gridStyle}>
          <AgGridReact<IOlympicData>
            ref={gridRef}
            columnDefs={columnDefs}
            defaultColDef={defaultColDef}
            rowModelType={"serverSide"}
            cacheBlockSize={100}
            maxBlocksInCache={10}
            onGridReady={onGridReady}
            onFilterChanged={onFilterChanged}
          />
        </div>
      </div>
    </AgGridProvider>
  );
};

const root = createRoot(document.getElementById("root")!);
root.render(
  <StrictMode>
    <GridExample />
  </StrictMode>,
);

fakeServer.tsx

// 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;
    }
}

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.

index.tsx

("use client");

import React, {
  useCallback,
  useMemo,
  useRef,
  useState,
  StrictMode,
} from "react";
import { createRoot } from "react-dom/client";
import { AgGridReact, AgGridProvider } from "ag-grid-react";
import {
  ColDef,
  ColGroupDef,
  FilterWrapperParams,
  GridApi,
  GridOptions,
  GridReadyEvent,
  IServerSideDatasource,
  ModuleRegistry,
  NumberFilterModule,
  RowModelType,
  SideBarDef,
  TextFilterModule,
  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") {
  enableDevValidations();
}

const modules = [
  ColumnMenuModule,
  ContextMenuModule,
  ServerSideRowModelModule,
  TextFilterModule,
  NumberFilterModule,
  SideBarModule,
  NewFiltersToolPanelModule,
];

const getServerSideDatasource: (server: any) => IServerSideDatasource = (
  server: any,
) => {
  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 GridExample = () => {
  const containerStyle = useMemo(() => ({ width: "100%", height: "100%" }), []);
  const gridStyle = useMemo(() => ({ height: "100%", width: "100%" }), []);

  const [columnDefs, setColumnDefs] = useState<ColDef[]>([
    {
      field: "athlete",
      cellDataType: "text",
      minWidth: 220,
    },
    { field: "year", cellDataType: "number" },
    { field: "gold", cellDataType: "number" },
    { field: "silver", cellDataType: "number" },
    { field: "bronze", cellDataType: "number" },
  ]);
  const defaultColDef = useMemo<ColDef>(() => {
    return {
      flex: 1,
      minWidth: 100,
      filter: true,
      filterParams: {
        buttons: ["apply"], // set all filters to use buttons
      } as FilterWrapperParams,
      suppressHeaderMenuButton: true,
      suppressHeaderContextMenu: true,
    };
  }, []);
  const sideBar = useMemo<
    SideBarDef | string | string[] | boolean | null
  >(() => {
    return {
      toolPanels: [
        {
          id: "filters-new",
          labelDefault: "Filters",
          labelKey: "filters",
          iconKey: "filter",
          toolPanel: "agNewFiltersToolPanel",
          toolPanelParams: {
            buttons: ["reset", "apply"],
          },
        },
      ],
      defaultToolPanel: "filters-new",
    };
  }, []);

  const onGridReady = useCallback((params: GridReadyEvent) => {
    fetch("https://www.ag-grid.com/example-assets/olympic-winners.json")
      .then((resp) => resp.json())
      .then((data: IOlympicData[]) => {
        // 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
        params.api!.setGridOption("serverSideDatasource", datasource);
      });
  }, []);

  return (
    <AgGridProvider modules={modules}>
      <div style={containerStyle}>
        <div style={gridStyle}>
          <AgGridReact<IOlympicData>
            columnDefs={columnDefs}
            defaultColDef={defaultColDef}
            rowModelType={"serverSide"}
            enableFilterHandlers={true}
            suppressSetFilterByDefault={true}
            sideBar={sideBar}
            onGridReady={onGridReady}
          />
        </div>
      </div>
    </AgGridProvider>
  );
};

const root = createRoot(document.getElementById("root")!);
root.render(
  <StrictMode>
    <GridExample />
  </StrictMode>,
);

fakeServer.tsx

// 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;
    }
}

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.

index.tsx

("use client");

import React, {
  useCallback,
  useMemo,
  useRef,
  useState,
  StrictMode,
} from "react";
import { createRoot } from "react-dom/client";
import { AgGridReact, AgGridProvider } from "ag-grid-react";
import {
  ColDef,
  ColGroupDef,
  GridApi,
  GridOptions,
  GridReadyEvent,
  IServerSideDatasource,
  ModuleRegistry,
  NumberFilterModule,
  RowModelType,
  TextFilterModule,
  enableDevValidations,
} from "ag-grid-community";
import {
  AdvancedFilterModule,
  ColumnMenuModule,
  ContextMenuModule,
  ServerSideRowModelModule,
} from "ag-grid-enterprise";
import { FakeServer } from "./fakeServer";
import { IOlympicData } from "./interfaces";

if (process.env.NODE_ENV !== "production") {
  enableDevValidations();
}

const modules = [
  TextFilterModule,
  NumberFilterModule,
  AdvancedFilterModule,
  ColumnMenuModule,
  ContextMenuModule,
  ServerSideRowModelModule,
];

const getServerSideDatasource: (server: any) => IServerSideDatasource = (
  server: any,
) => {
  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 GridExample = () => {
  const containerStyle = useMemo(() => ({ width: "100%", height: "100%" }), []);
  const gridStyle = useMemo(() => ({ height: "100%", width: "100%" }), []);

  const [columnDefs, setColumnDefs] = useState<ColDef[]>([
    {
      field: "athlete",
      cellDataType: "text",
      minWidth: 220,
    },
    {
      field: "year",
      cellDataType: "number",
    },
    {
      field: "gold",
      cellDataType: "number",
    },
    {
      field: "silver",
      cellDataType: "number",
    },
    {
      field: "bronze",
      cellDataType: "number",
    },
  ]);
  const defaultColDef = useMemo<ColDef>(() => {
    return {
      flex: 1,
      minWidth: 100,
      filter: true,
      suppressHeaderMenuButton: true,
      suppressHeaderContextMenu: true,
    };
  }, []);

  const onGridReady = useCallback((params: GridReadyEvent) => {
    fetch("https://www.ag-grid.com/example-assets/olympic-winners.json")
      .then((resp) => resp.json())
      .then((data: IOlympicData[]) => {
        // 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
        params.api!.setGridOption("serverSideDatasource", datasource);
      });
  }, []);

  return (
    <AgGridProvider modules={modules}>
      <div style={containerStyle}>
        <div style={gridStyle}>
          <AgGridReact<IOlympicData>
            columnDefs={columnDefs}
            defaultColDef={defaultColDef}
            rowModelType={"serverSide"}
            enableAdvancedFilter={true}
            onGridReady={onGridReady}
          />
        </div>
      </div>
    </AgGridProvider>
  );
};

const root = createRoot(document.getElementById("root")!);
root.render(
  <StrictMode>
    <GridExample />
  </StrictMode>,
);

fakeServer.tsx

// 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 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 {
                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);
        }
    }

    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;
    }
}

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
}

Note that Cell Data Types must be supplied in order for the Advanced Filter to display the correct filter options, otherwise only 'text' options will be displayed.

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

React 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