# Named Queries

Canonical page: https://www.adaptabletools.com/docs/handbook-named-queries

- Named Queries are Expressions which have been saved and named (or provided in Initial Adaptable State)
- This allows them to be (re-)used in Expressions across AdapTable via the `QUERY` function
- A Named Query can resolve to *any* value - boolean, number, text or date
- Named Queries which resolve to a boolean (true / false) can be used in, and loaded from, the Grid Filter Toolbar
- Developers can reduce Named Query complexity by limiting which Columns and Expression Functions are available

Most Expressions (e.g. [Grid Filters](https://www.adaptabletools.com/docs/handbook-grid-filter/index.md), [Conditional Styles](https://www.adaptabletools.com/docs/handbook-column-formatting-conditions/index.md) and [Row Change Alerts](https://www.adaptabletools.com/docs/handbook-alerting-row-change/index.md)) are typically created on the fly, evaluated by AdapTableQL immediately, and then cleared when no longer needed.

However, if required, Expressions can also be named and saved in order to be re-used at a later date.

In this scenario the Expression is called a **Named Query** and is saved into Named Query State.

The Named Query is then available for loading and running in the future (either in this or later sessions).

Named Queries can also be provided at design-time in [Initial Adaptable State](https://www.adaptabletools.com/docs/technical-reference-initial-state/index.md)

Run-time users can also save a Grid Filter as a Named Query from the UI. See [Managing Grid Filters](https://www.adaptabletools.com/docs/handbook-grid-filter-managing/index.md).

A Named Query's Expression can resolve to _any_ value, for example:

- **Boolean** — (e.g. `[github_stars] > 10000`) used anywhere a Boolean Expression is expected (e.g. Alerts)
  
  Boolean Named Queries can also be loaded as a [Grid Filter](https://www.adaptabletools.com/docs/handbook-grid-filter/index.md) (as filtering requires a true / false result per row)
  
- **Numeric** — (e.g. `PRODUCT([github_stars], [github_watchers], [open_issues_count])`) wrapped in a [Calculated Column](https://www.adaptabletools.com/docs/handbook-calculated-column/index.md) via `QUERY`
- **Text** — (e.g. `LOWER([name])`) can also be wrapped in a [Calculated Column](https://www.adaptabletools.com/docs/handbook-calculated-column/index.md)

- Numeric and text Named Queries centralise calculation logic once so you can re-use it with the `QUERY` function
- This common pattern defines a **scalar** Named Query and references it from a [Calculated Column](https://www.adaptabletools.com/docs/handbook-calculated-column/index.md)

## Defining Named Queries

Named Queries can be provided at design-time through [Named Query Initial Adaptable State](https://www.adaptabletools.com/docs/technical-reference-named-query/index.md).

They are then persisted into Adaptable State and available to be run when the Application starts.

### Providing Named Queries in Initial State

Follow these steps to add Named Queries to Initial Adaptable State.

Add the `NamedQuery` section to Adaptable Initial State.

It contains a `NamedQueries` property — an array of [`NamedQuery`](https://www.adaptabletools.com/docs/reference/namedquery.md) objects.

Each Named Query has a unique `Name`.

The name appears in Grid Filter UI (toolbar, tool panel, status bar) and in `QUERY('…')` references elsewhere.

Each Named Query contains an [Expression](https://www.adaptabletools.com/docs/adaptable-ql-expression/index.md) which can resolve to any value (Boolean, number, text or date).

AdapTable evaluates it with [AdapTableQL](https://www.adaptabletools.com/docs/adaptable-ql-expression/index.md).

```ts [[1, 3, "NamedQuery"], [1, 4, "NamedQueries"], [2, 6, "Name"], [2, 11, "Name"], [2, 16, "Name"], [2, 20, "Name"], [3, 7, "Expression"], [3, 12, "Expression"], [3, 17, "Expression"], [3, 21, "Expression"]]
// Four Named Queries on the Web Frameworks dataset (three boolean, one numeric)
const initialState: InitialState = {
  NamedQuery: {
    NamedQueries: [
      {
        Name: 'Hottest JavaScript',
        Expression:
          '[language]="JavaScript" AND ([github_watchers] > 2000 OR [github_stars] > 14500)',
      },
      {
        Name: 'Well Maintained',
        Expression:
          '[week_issue_change] > 0 AND CONTAINS([license], "MIT") AND [has_wiki]',
      },
      {
        Name: 'Many Stars',
        Expression: '[github_stars] > 10000 AND [open_issues_count] > 100',
      },
      {
        Name: 'Popularity Score',
        Expression:
          'PRODUCT([github_stars], [github_watchers], [open_issues_count])',
      },
    ],
  },
};
```

**Example: Named Queries**

Defining and Saving "Named Queries" for Re-Use

- This example provides 2 Named Queries in Initial State: `Hottest JavaScript` and `Well Maintained`
- Both appear in the Grid Filter dropdown at the end of the Grid Filter toolbar / tool panel
- A custom Dashboard toolbar (`Queries`) runs each Named Query via [Named Query API](https://www.adaptabletools.com/docs/technical-reference-named-query/index.md) — loading the stored **Expression** into the Grid Filter (you do not need `QUERY('…')` in that code path)
- See `adaptableOptions.ts` in the demo tab

- Click **Load Query** at the right-hand end of the Grid Filter tool panel and pick a Named Query
- Or click **Hottest JavaScript** or **Well Maintained** on the **Queries** custom toolbar

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

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts       [template]
  index.ts             [template]
  onAdaptableReady.ts  [template]
  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 {NamedQuery} from '@adaptabletools/adaptable';
import {
  AdaptableButton,
  AdaptableOptions,
  CustomToolbarButtonContext,
} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Named Queries',
  dashboardOptions: {
    customToolbars: [
      {
        name: 'ButtonToolbar',
        title: 'Queries',
        toolbarButtons: [
          {
            label: 'Hottest JavaScript',
            onClick: (
              button: AdaptableButton<CustomToolbarButtonContext>,
              context: CustomToolbarButtonContext
            ) => {
              const namedQuery: NamedQuery | undefined =
                context.adaptableApi.namedQueryApi.getNamedQueryByName(
                  'Hottest JavaScript'
                );
              if (namedQuery) {
                context.adaptableApi.filterApi.gridFilterApi.setGridFilterExpression(
                  namedQuery.Expression
                );
              }
            },
          },
          {
            label: 'Well Maintained',
            onClick: (
              button: AdaptableButton<CustomToolbarButtonContext>,
              context: CustomToolbarButtonContext
            ) => {
              const namedQuery: NamedQuery | undefined =
                context.adaptableApi.namedQueryApi.getNamedQueryByName(
                  'Well Maintained'
                );
              if (namedQuery) {
                context.adaptableApi.filterApi.gridFilterApi.setGridFilterExpression(
                  namedQuery.Expression
                );
              }
            },
          },
        ],
      },
    ],
  },
  initialState: {
    Dashboard: {
      PinnedToolbars: ['GridFilter'],
      Tabs: [
        {
          Name: 'Demo',
          Toolbars: ['ButtonToolbar'],
        },
      ],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['GridFilter'],
        },
      ],
    },
    NamedQuery: {
      NamedQueries: [
        {
          Name: 'Hottest JavaScript',
          Expression:
            '[language]="JavaScript" AND ([github_watchers] > 2000 OR [github_stars] > 14500)',
        },
        {
          Name: 'Well Maintained',
          Expression:
            '[week_issue_change] > 0 AND CONTAINS([license], "MIT") AND [has_wiki]',
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'github_watchers',
            'topics',
            'license',
            'created_at',
            'has_wiki',
            'updated_at',
            'pushed_at',
            'description',
            'open_issues_count',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
  },
};
```

## Referencing Named Queries

A Named Query can be directly referenced in other Expressions (e.g. in [Column Formatting](https://www.adaptabletools.com/docs/handbook-column-formatting/index.md) or [Alerts](https://www.adaptabletools.com/docs/handbook-alerting/index.md)).

This is achieved by using the [QUERY](https://www.adaptabletools.com/docs/adaptable-ql-expression-advanced-query-function/index.md) AdapTableQL function.

The `QUERY` function can be used in:

- other Named Queries
- other Expressions (e.g. Alerts, Format Columns)
- the **Grid Filter** saved on the current Layout

### Referencing Named Queries in Initial State

Define Named Queries first, then reference them with `QUERY('Name')` in Layouts, Format Columns, Calculated Columns, or nested Named Queries.

Add entries to `NamedQuery.NamedQueries` in Adaptable State, each with a `Name` and `Expression`.

Use `QUERY('Name')` in Grid Filter, Format Column rules, Calculated Column expressions, and similar.

Combine Named Queries by referencing them from another Named Query's Expression.

```ts [[1, 4, "NamedQueries"], [1, 7, "Expression"], [1, 12, "Expression"], [1, 16, "Expression"], [1, 20, "Expression"], [3, 16, "QUERY('Many Stars')"], [3, 16, "QUERY('Hottest JavaScript')", 28], [2, 31, "QUERY"], [2, 41, "QUERY"], [2, 50, "QUERY"]]
// Named Queries on Web Frameworks data; consumed via QUERY in Layout, Format Column & Calculated Column
const initialState: InitialState = {
  NamedQuery: {
    NamedQueries: [
      {
        Name: 'Hottest JavaScript',
        Expression:
          '[language]="JavaScript" AND ([github_watchers] > 2000 OR [github_stars] > 14500)',
      },
      {
        Name: 'Many Stars',
        Expression: '[github_stars] > 10000 AND [open_issues_count] > 100',
      },
      {
        Name: 'Hottest And Many Stars',
        Expression: "QUERY('Many Stars') AND QUERY('Hottest JavaScript')",
      },
      {
        Name: 'Popularity Score',
        Expression:
          'PRODUCT([github_stars], [github_watchers], [open_issues_count])',
      },
    ],
  },
  Layout: {
    CurrentLayout: 'Standard Layout',
    Layouts: [
      {
        Name: 'Standard Layout',
        TableColumns: ['name', 'github_stars', 'github_watchers', 'popularity-score'],
        GridFilter: { Expression: "QUERY('Hottest And Many Stars')" },
      },
    ],
  },
  FormatColumn: {
    FormatColumns: [
      {
        Name: 'Hottest JavaScript Style',
        Scope: { All: true },
        Style: { FontWeight: 'Bold' },
        Rule: 'QUERY("Hottest JavaScript")',
      },
    ],
  },
  CalculatedColumn: {
    CalculatedColumns: [
      {
        ColumnId: 'popularity-score',
        FriendlyName: 'Popularity Score',
        Expression: "QUERY('Popularity Score')",
        CalculatedColumnSettings: { DataType: 'number' },
      },
    ],
  },
};
```

**Example: Referencing Named Queries**

Referencing Named Queries by using the QUERY keyword

- This example references Named Queries from other Expressions (see `adaptableOptions.ts`)
- **`Hottest JavaScript`** — Boolean; used in the Layout **Grid Filter** (with an extra `CONTAINS` on `[topics]`) and in a **Format Column** rule
- **`Popularity Score`** — numeric `PRODUCT` expression; exposed as a **Calculated Column** via `QUERY('Popularity Score')`

- Clear the Grid Filter with **x** on the Grid Filter toolbar — the Format Column still highlights matching rows
- Reload a Named Query from the Grid Filter dropdown
- Inspect the **Popularity Score** column — values come from the Named Query

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

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts       [template]
  index.ts             [template]
  onAdaptableReady.ts  [template]
  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} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Referencing Named Queries',
  initialState: {
    Dashboard: {
      PinnedToolbars: ['GridFilter'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['GridFilter'],
        },
      ],
    },
    NamedQuery: {
      NamedQueries: [
        {
          Name: 'Hottest JavaScript',
          Expression:
            '[language]="JavaScript" AND ([github_watchers] > 2000 OR [github_stars] > 14500)',
        },
        {
          Name: 'Popularity Score',
          Expression:
            'PRODUCT([github_stars], [github_watchers], [open_issues_count])',
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'popularity-score',
          FriendlyName: 'Popularity Score',
          Expression: "QUERY('Popularity Score')",
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-all',
          Scope: {All: true},
          Rule: 'QUERY("Hottest JavaScript")',
          Style: {
            ForeColor: 'var(--ab-color-palette-14)',
            BackColor: 'var(--ab-color-palette-17)',
          },
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'github_watchers',
            'topics',
            'license',
            'created_at',
            'has_wiki',
            'updated_at',
            'pushed_at',
            'description',
            'open_issues_count',
            'popularity-score',
          ],
          GridFilter: {
            Expression:
              'QUERY("Hottest JavaScript") AND CONTAINS([topics] , "javascript") ',
          },
          AutoSizeColumns: true,
        },
      ],
    },
  },
};
```

## Limiting Named Queries

[Expression Options](https://www.adaptabletools.com/docs/technical-reference-expression/index.md) provides 2 properties which allow users to limit which elements can be contained in a Named Query's Expression:

- This scenario typically occurs when the Expression containing the Named Query is [being evaluated remotely](https://www.adaptabletools.com/docs/adaptable-server-evaluation/index.md)
- For example, if you are evaluating the Grid Filter on your server (rather than locally by AdapTable)

- `isExpressionFunctionAvailable` — function which specifies which Expression Functions are available (globally, per Module or per Column)

- `isColumnQueryable` — function which specifies whether a given Column should be included in Expressions

See [Reducing Expression Complexity](https://www.adaptabletools.com/docs/adaptable-ql-expression-managing/index.md) for more information
