Named Queries

View as Markdown

Summary

  • 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, Conditional Styles and Row Change Alerts) 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).

Hint

Named Queries can also be provided at design-time in Initial Adaptable State

Find Out More

Run-time users can also save a Grid Filter as a Named Query from the UI. See Managing Grid Filters.

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)

    Note

    Boolean Named Queries can also be loaded as a Grid Filter (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 via QUERY
  • Text — (e.g. LOWER([name])) can also be wrapped in a Calculated Column

Hint

  • 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

Defining Named Queries

Named Queries can be provided at design-time through Named Query Initial Adaptable State.

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

Developer Guide

Providing Named Queries in Initial State

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

// 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])',
      },
    ],
  },
};
1
Provide Named Queries

Add the NamedQuery section to Adaptable Initial State.

It contains a NamedQueries property — an array of NamedQuery objects.

2
Give each Named Query a Name

Each Named Query has a unique Name.

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

3
Write the Expression

Each Named Query contains an Expression which can resolve to any value (Boolean, number, text or date).

AdapTable evaluates it with AdapTableQL.

Named Queries
Fork
  • 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 — loading the stored Expression into the Grid Filter (you do not need QUERY('…') in that code path)
  • See adaptableOptions.ts in the demo tab
Try It Out
  • 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
Loading demo…

Referencing Named Queries

A Named Query can be directly referenced in other Expressions (e.g. in Column Formatting or Alerts).

This is achieved by using the QUERY 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
Developer Guide

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.

// 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' },
      },
    ],
  },
};
1
Create Named Query

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

2
Reference Named Query in other Objects

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

3
Reference Named Query in other Queries

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

Referencing Named Queries
Fork
  • 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')
Try It Out
  • 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
Loading demo…

Limiting Named Queries

Expression Options provides 2 properties which allow users to limit which elements can be contained in a Named Query's Expression:

Note

  • This scenario typically occurs when the Expression containing the Named Query is being evaluated remotely
  • 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

Find Out More

See Reducing Expression Complexity for more information