AdapTable Version:

Framework:

Named Queries

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
Named Queries can be loaded from the Grid Filter Toolbar
Developers can reduce the complexity of Named Queries in 2 ways:
- Limiting how many columns can be referenced
- Limiting how many Expression Functions are available

Most Expressions (e.g. Grid Filters, Column Formatting Conditions and Row Change Alerts) are typically created on the fly, evaluated by AdapTableQL immediatedly, and then cleared when no longer needed.

However, if required, (Boolean) 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.

This named Query will then be 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

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 define Queries in Initial Adaptable State

// We provide 3 Named Queries - each with a Name and Boolean Expression
const initialState: InitialState = {
  NamedQuery: {
    NamedQueries: [
    {
        Name: 'US Banks',
        BooleanExpression: "[cpty] IN ('BAML', 'Citi') AND ([bid] > 3"
    },
    {
        Name: 'Non UP Shipping',
        BooleanExpression: '[shipvia] != "United Package" '
    },
    {
        Name: 'Big Orders',
        BooleanExpression: '[change] > 10 AND [packageCost] > 10'
    },
  ]},
}

Provide Named Queries

Add the NamedQuery section to Adaptable Initial State.

It contains a NamedQueries propery - an array of NamedQuery objects

Give each Named Query a Name

Each Named Query has a unique Name.

This enables the NamedQuery to be referenced in the UI (e.g. NamedQuery Toolbar, ToolPanel and StatusPanel) to reference it in other Queries

Write the Expression

Each Named Query contains a Boolean Expression

This will be evaluated by AdapTableQL

Named Queries

Fork Fork

In this example we provide 2 Named Queries in Named Query Initial State:
- Hottest JavaScript
- Well Maintained
Both are available by clicking the dropdown button at the end of the Grid Filter Toolbar
We have also created a Custom Dashboard Toolbar and made the Named Queries available as Buttons

Try It Out

Click the 'Load Query' button at the right hand end of the Query ToolPanel to see a list of Named Queries
Select one of the Items in the Dropdown and see how AdapTable automatically runs it and filters the grid
Alternatively click a button in the Queries Custom Toolbar to run the Query

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.BooleanExpression
                );
              }
            },
          },
          {
            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.BooleanExpression
                );
              }
            },
          },
        ],
      },
    ],
  },
  initialState: {
    Dashboard: {
      PinnedToolbars: ['GridFilter'],
      Tabs: [
        {
          Name: 'Demo',
          Toolbars: ['ButtonToolbar'],
        },
      ],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['GridFilter'],
        },
      ],
    },
    NamedQuery: {
      NamedQueries: [
        {
          Name: 'Hottest JavaScript',
          BooleanExpression:
            '[language]="JavaScript" AND ([github_watchers] > 2000 OR [github_stars] > 14500)',
        },
        {
          Name: 'Well Maintained',
          BooleanExpression:
            '[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 or Alerts)

This is achieved by using the QUERY AdapTableQL Expression Function.

in other Named Queries
in other Expressions (e.g. Alerts)
in the Grid Filter which is saved in the Current Layout)

Developer Guide

Referencing Named Queries in Initial State

Follow these steps to define Queries in Initial Adaptable State

// We provide 2 Named Queries (one of which references the other Named Queries)
// These Named Queries are then referencd in the Grid Filter and a FormatColumn
const initialState: InitialState = {
  NamedQuery: {
    NamedQueries: [
    {
        Name: 'Big Orders',
        BooleanExpression: '[change] > 10 AND [packageCost] > 10'
    },
    {
        Name: 'Big US Orders',
        BooleanExpression: "QUERY('Big Orders') AND [price] > 1000"
    },
  ]},
  FormatColumn: {
      FormatColumns: [{
          Name: 'FormatColumn-all-210',
          Scope: { All: true },
          Style: { FontWeight: 'Bold' },
          Rule:{ BooleanExpression: "QUERY('US Banks')" }
        }],
    },
    Layout: {
      CurrentLayout: 'Simple Layout',
      Layouts: [{
          Name: 'Simple Layout',
          TableColumns: ['price', 'change', 'packageCost'],
          GridFilter: { Expression: 'QUERY("Non UP Shipping")' },
        }],
    }, 
  },
}

Create Named Query

Add the Named Query to NamedQueries section of Adaptable State

Reference Named Query in other Objects

Reference the Named Query in other AdapTable Expressions

This is done by using the QUERY property

Reference Named Query in other Queries

The Named Query can even be referenced in other Named Queries

Referencing Named Queries

Fork Fork

This example shows how to reference Named Queries in other Expressions in AdapTable
We have created the same Named Query called Hottest JavaScript as in the demo above
But we then reference that Named Query in 2 places:
- In the Grid Filter's Expression
- In a Format Column where we style with a back colour of brown any rows that match

Expand to see how the Named Query is referenced

Creating the Named Query:

NamedQuery: {
   NamedQueries: [
     {
       Name: 'Hottest JavaScript',
       BooleanExpression: '[language]="JavaScript" AND ([github_watchers] > 2000 OR [github_stars] > 14500)',
     },
   ],
 },

Referencing the NamedQuery in the Grid Filter:

Layout: {
  GridFilter: {
    Expression: 'QUERY("Hottest JavaScript") AND CONTAINS([topics] , "javascript") ',
  },
},

And also in a Format Column:

FormatColumn: {
  FormatColumns: [
    {
      Name: 'FormatColumn-all-211',
      Scope: {All: true},
      Rule: { BooleanExpression: 'QUERY("Hottest JavaScript")' },
      Style: {
        ForeColor: 'White',
        BackColor: 'Brown',
      },
    },
  ],
},

Try It Out

Clear the Grid Filter by clicking the 'x' in the Grid Filter toolbar and you will see the Format Column still paints the matching rows
You can then reload the Named Query from the dropdown in the Grid Filter Toolbar

Limiting Named Queries

Expression Options provides 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 evalated the Grid Filter on your server (rather than locally by AdapTable)

A series of properties (e.g. systemBooleanFunctions, systemObservableFunctions etc.) allows specified Expression Functions set to be provided to (or omitted from) AdapTable
isColumnQueryable - function which specifies whether a given Column should be included in Expressions

Find Out More

See Reducing Expression Complexity for more information

AdapTable Resources

PreviousTechnical Reference NextTechnical Reference

Framework: