# Server-Side Row Model - Grid Filter

Canonical page: https://www.adaptabletools.com/docs/dev-guide-row-models-server-grid-filter

- The Grid Filter needs to be evaluated remotely when using the Server-Side Row Model
- AdapTable provides the current Grid Filter Expression and AST so you can pass them to the server
- Custom Expression Functions and Named Queries work the same way — only the evaluation is remote

When using the Server-Side Row Model, AG Grid **will not perform any filtering**.

The [Grid Filter](https://www.adaptabletools.com/docs/handbook-grid-filter/index.md) can still be applied, but instead of [AdapTableQL](https://www.adaptabletools.com/docs/adaptable-ql-expression/index.md) evaluating the [Expression](https://www.adaptabletools.com/docs/adaptable-ql-expression/index.md) automatically, it is performed by developers on the server.

- The Grid Filter can also include [Custom Expression Functions](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom/index.md) provided by developers
- These will also then need to be evaluated remotely in a bespoke implementation

[Column Filtering](https://www.adaptabletools.com/docs/dev-guide-row-models-server-filtering/index.md) covers Column Filters, In Filter values, and how to retrieve Filter State via `getAdaptableFilterState`

## Getting Filter State

Inside `getRows`, send the current Grid Filter to the server using `getAdaptableFilterState` in the [State API](https://www.adaptabletools.com/docs/technical-reference-adaptable-state/index.md#state-api).

The returned [`AdaptableFilterState`](https://www.adaptabletools.com/docs/reference/adaptablefilterstate.md) includes `gridFilter` (the Expression string) and `gridFilterAST` (the AST to evaluate remotely).

The [Grid Filter Applied](https://www.adaptabletools.com/docs/technical-reference-filter/index.md) Event still fires, but is rarely subscribed to when using the SSRM

## Limiting Expression Complexity

By default AdapTable will provide every [AdapTableQL Expression Functions](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions/index.md) in the [Expression Editor](https://www.adaptabletools.com/docs/ui-expression-editor/index.md).

This means bespoke server implementations will need to translate **every** Expression into something meaningful.

Accordingly, AdapTable allows developers to [reduce Expression complexity](https://www.adaptabletools.com/docs/adaptable-ql-expression-managing/index.md) in 2 ways in order to make the server implementation more manageable:
- Limiting which **Expressions** are available (on a *per Module basis* if required)
- Limiting which **Columns** can be included in a Query / Expression

**Example: SSRM - Grid Filter**

Server-Side Row Model: Applying Grid Filter

- The example shows how the AdapTable Grid Filter can still be used while using Server-Side Row Model
- We include 3 [Named Queries ](https://www.adaptabletools.com/docs/handbook-named-queries/index.md) which are evaluated on our mock server (and accessible from dropdown at end of toolbar):
  - `US Golds` - returns rows where USA has > 1 gold
  - `European Athletes` - returns rows where country is in Europe (and uses a [Custom Boolean Expression Function](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom/index.md), `FROM_EUROPE`, that is also evaluated on our mock server)
  - `5 Medals` - returns rows where Gold + Silver + Bronze is greater than 5
- We provide an implementation of `isExpressionFunctionAvailable` in [Expression Options](https://www.adaptabletools.com/docs/technical-reference-expression/index.md) to show how to limit which Grid Filter expression functions are available

- Apply the 3 Named Queries from the Grid Filter toolbar (or write your own) and note the SQL sent to the server

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts
  gridOptions.ts
  index.ts             [template]
  onAdaptableReady.ts
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
import {
  AdaptableOptions,
  GlobalExpressionFunctionAvailabilityContext,
} from '@adaptabletools/adaptable';

const gridFilterFunctions = [
  'EQ',
  'NEQ',
  'GT',
  'LT',
  'GTE',
  'LTE',
  'AND',
  'OR',
  'NOT',
  'IN',
  'NOT_IN',
  'CONTAINS',
  'NOT_CONTAINS',
  'STARTS_WITH',
  'ENDS_WITH',
  'REGEX',
  'IS_BLANK',
  'IS_NOT_BLANK',
  'IS_POSITIVE',
  'IS_NEGATIVE',
  'SUM',
  'SUB',
  'PRODUCT',
  'DIV',
  'IS_TODAY',
  'IS_YESTERDAY',
  'IS_TOMORROW',
  'IS_BEFORE_TODAY',
  'IS_AFTER_TODAY',
  'IS_PAST',
  'IS_FUTURE',
  'IS_THIS_WEEK',
  'IS_THIS_MONTH',
  'IS_THIS_QUARTER',
  'IS_THIS_YEAR',
  'IS_WORKDAY',
  'IS_HOLIDAY',
  'IS_NEXT_WORKDAY',
  'IS_PREV_WORKDAY',
  'IS_SAME_DAY',
  'IS_DIFFERENT_DAY',
  'DATE',
  'TODAY',
  'CURRENT_DAY',
  'COL',
  'FROM_EUROPE',
];

export const adaptableOptions: AdaptableOptions = {
  primaryKey: 'id',
  adaptableId: 'SSRM - Grid Filtering',
  expressionOptions: {
    customStandardFunctions: {
      FROM_EUROPE: {
        // handled on the server
        handler: () => true,
        category: 'custom',
        returnType: 'boolean',
        operands: [['text']],
        description: 'Returns true if the country is from Europe',
        signatures: ['FROM_EUROPE([country])'],
        isAvailable: ['GridFilter'],
      },
    },
    isExpressionFunctionAvailable: (
      context: GlobalExpressionFunctionAvailabilityContext
    ) => {
      if (context.module === 'GridFilter') {
        return gridFilterFunctions.includes(context.functionName);
      }
      return context.defaultAvailability;
    },
  },
  initialState: {
    Dashboard: {
      Tabs: [
        {
          Name: 'Main',
          Toolbars: ['GridFilter', 'SystemStatus'],
        },
      ],
    },
    Theme: {
      CurrentTheme: 'dark',
    },
    Layout: {
      CurrentLayout: 'SSRM Layout',
      Layouts: [
        {
          Name: 'SSRM Layout',
          TableColumns: [
            'athlete',
            'gold',
            'silver',
            'bronze',
            'country',
            'sport',
            'year',
          ],
          ColumnSizing: {
            athlete: {Width: 175},
            bronze: {Width: 100},
            country: {Width: 125},
            gold: {Width: 100},
            silver: {Width: 100},
            sport: {Width: 175},
            year: {Width: 115},
          },
        },
      ],
    },
    NamedQuery: {
      NamedQueries: [
        {
          Name: 'US Golds',
          Expression: '[country]="United States" AND [gold] > 1',
        },
        {
          Name: 'European Athletes',
          Expression: 'FROM_EUROPE([country])',
        },
        {
          Name: '5 Medals',
          Expression: '[gold] + [silver] + [bronze] > 5',
        },
      ],
    },
  },
};
```

**`src/onAdaptableReady.ts`**

```ts
import {AdaptableReadyInfo, AdaptableApi} from '@adaptabletools/adaptable';
import {
  ColDef,
  GridApi,
  IServerSideDatasource,
  IServerSideGetRowsParams,
  IServerSideGetRowsRequest,
} from 'ag-grid-enterprise';

export const onAdaptableReady = ({
  adaptableApi,
  agGridApi,
}: AdaptableReadyInfo) => {
  agGridApi.setGridOption(
    'serverSideDatasource',
    createDataSource(adaptableApi)
  );
};

const API_BASE: string = process.env.NEXT_PUBLIC_ATHLETES_API_URL!;

// v24 Column Filters are Expressions. The athletes mock server evaluates
// both Grid Filter and Column Filters from `gridFilterAST`.
function rewriteAutoGroupColumn(
  node: unknown,
  groupedColumnId?: string
): unknown {
  if (!groupedColumnId || node == null || typeof node !== 'object') {
    return node;
  }
  if (Array.isArray(node)) {
    return node.map(item => rewriteAutoGroupColumn(item, groupedColumnId));
  }
  const astNode = node as {type?: string; args?: unknown[]};
  if (astNode.type === 'COL' && astNode.args?.[0] === 'ag-Grid-AutoColumn') {
    return {
      ...astNode,
      args: [groupedColumnId, ...astNode.args.slice(1)],
    };
  }
  if (!astNode.args) {
    return node;
  }
  return {
    ...astNode,
    args: astNode.args.map(arg =>
      rewriteAutoGroupColumn(arg, groupedColumnId)
    ),
  };
}

function appendExpressionAst(
  astNodes: unknown[],
  ast: unknown,
  groupedColumnId?: string
) {
  if (ast == null) {
    return;
  }
  const nodes = Array.isArray(ast) ? ast : [ast];
  for (const node of nodes) {
    astNodes.push(rewriteAutoGroupColumn(node, groupedColumnId));
  }
}

function toServerFilterFields(
  adaptableApi: AdaptableApi,
  groupedColumnId?: string
) {
  const filterState = adaptableApi.stateApi.getAdaptableFilterState();
  const astNodes: unknown[] = [];
  appendExpressionAst(astNodes, filterState.gridFilterAST, groupedColumnId);
  for (const columnFilter of filterState.columnFilters ?? []) {
    if (!columnFilter.Expression) {
      continue;
    }
    appendExpressionAst(
      astNodes,
      adaptableApi.expressionApi.getASTForExpression(columnFilter.Expression),
      groupedColumnId
    );
  }
  return {
    adaptableFilters: [],
    gridFilterAST: astNodes.length ? astNodes : undefined,
  };
}

function createDataSource(adaptableApi: AdaptableApi): IServerSideDatasource {
  return {
    getRows(params: IServerSideGetRowsParams) {
      const getRowsRequest: IServerSideGetRowsRequest = params.request;
      const adaptableSortState = adaptableApi.stateApi.getAdaptableSortState();
      const groupedColumn = getRowsRequest.rowGroupCols?.[0];
      const serverFilters = toServerFilterFields(
        adaptableApi,
        groupedColumn?.field ?? groupedColumn?.id
      );

      const sortModel = getRowsRequest.sortModel.map(sort => {
        const customSort = adaptableSortState.customSorts.find(
          customSort => customSort.ColumnId === sort.colId
        );
        if (customSort) {
          return {
            ...sort,
            sortedValues: customSort.SortedValues,
          };
        }
        return sort;
      });

      const request = {
        ...getRowsRequest,
        sortModel,
        ...serverFilters,
        includeSQL: true,
      };
      console.log('request to server', request);

      fetch(API_BASE, {
        method: 'post',
        body: JSON.stringify(request),
        headers: {'Content-Type': 'application/json; charset=utf-8'},
      })
        .then(httpResponse => httpResponse.json())
        .then(response => {
          params.success({
            rowData: response.rows,
            rowCount: response.lastRow ?? 0,
          });

          if (response.pivotFields?.length) {
            addPivotColumnDefs(response, params.api);
          } else {
            if (!adaptableApi.layoutApi.isCurrentLayoutPivot()) {
              if (params.api.getPivotResultColumns() != null) {
                params.api.setPivotResultColumns(null);
              }
            }
          }

          adaptableApi.systemStatusApi.setInfoSystemStatus(
            `SQL: ${response.sql.slice(0, 40)}`,
            response.sql
          );
        })
        .catch(error => {
          params.fail();
        });
    },
  };
}

function addPivotColumnDefs(response: any, agGridApi: GridApi) {
  const existingPivotColDefs = agGridApi.getPivotResultColumns();
  if (existingPivotColDefs && existingPivotColDefs.length > 0) {
    return;
  }

  const pivotColDefs: ColDef[] = response.pivotFields.map(function (
    field: any,
    index: number
  ) {
    const [_key, value] = Object.entries(field)[0];
    const valueStr = `${value}`;
    return {
      headerName: valueStr,
      field: valueStr,
      filter: false,
      colId: `${Date.now()}_${index}`,
      cellDataType: 'number',
    };
  });

  agGridApi.setPivotResultColumns(pivotColDefs);
}
```

**`src/gridOptions.ts`**

```ts
import {GridOptions, themeBalham} from 'ag-grid-enterprise';

import {columnDefs} from './columnDefs';

export const gridOptions: GridOptions = {
  theme: themeBalham,
  defaultColDef: {
    sortable: true,
    filter: true,
    floatingFilter: true,
    enableRowGroup: true,
  },
  rowModelType: 'serverSide',
  columnDefs: columnDefs,
  sideBar: ['adaptable', 'columns', 'filters'],
};
```

**`src/columnDefs.ts`**

```ts
import {ColDef} from 'ag-grid-enterprise';

export const columnDefs: ColDef[] = [
  {field: 'id', hide: true, cellDataType: 'text'},
  {
    field: 'athlete',
    cellDataType: 'text',
    enableRowGroup: true,
    resizable: true,
  },
  {
    field: 'country',
    cellDataType: 'text',
    enableRowGroup: true,
    resizable: true,
  },
  {
    field: 'sport',
    cellDataType: 'text',
    enableRowGroup: true,
    resizable: true,
  },
  {
    field: 'year',
    cellDataType: 'number',
    enableRowGroup: true,
    resizable: true,
  },
  {
    field: 'gold',
    aggFunc: 'sum',
    cellDataType: 'number',
    enableValue: true,
    resizable: true,
  },
  {
    field: 'silver',
    aggFunc: 'sum',
    cellDataType: 'number',
    enableValue: true,
    resizable: true,
  },
  {
    field: 'bronze',
    aggFunc: 'sum',
    cellDataType: 'number',
    enableValue: true,
    resizable: true,
  },
];
```