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
QUERYfunction - 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 viaQUERY - 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
QUERYfunction - 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])',
},
],
},
};Add the NamedQuery section to Adaptable Initial State.
It contains a NamedQueries property — an array of NamedQuery 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 which can resolve to any value (Boolean, number, text or date).
AdapTable evaluates it with AdapTableQL.
- This example provides 2 Named Queries in Initial State:
Hottest JavaScriptandWell 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 needQUERY('…')in that code path) - See
adaptableOptions.tsin 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
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' },
},
],
},
};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.
- This example references Named Queries from other Expressions (see
adaptableOptions.ts) Hottest JavaScript— Boolean; used in the Layout Grid Filter (with an extraCONTAINSon[topics]) and in a Format Column rulePopularity Score— numericPRODUCTexpression; exposed as a Calculated Column viaQUERY('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
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
AdapTable Resources
- Named Query Technical Reference
- Grid Filter
- Managing Grid Filters
- Calculated Column
- QUERY Expression Function
- AdapTableQL Expressions
- Team Sharing (referenced Named Queries in shared objects)