AdapTableQL (AQL)

AQL (or AdapTable Query Language) is an advanced, custom built Query Langugage.

It evaluates and executes complex queries (called expressions) defined at design or run time.

Note

AQL is designed and implemented to be fast, efficient and highly performant, and to be able to operate on very large data sets
It is widely used in AdapTable whenever data needs to be searched, filtered or derived

AQL is incredibly powerful, but also human readable, and can be used for:

configuring Calculated Columns
defining Column Filters and the Grid Filter
applying Conditional Styles

Important

The current AQL implemenation is a port of the library used in AdapTable for AG Grid
However, we are in the process of moving AdapTableQL into a standalone library (with its own documentation)
Both AdapTable extensions (for AG Grid and for Infinite Table) will access this common library (with separate UI implementations)

Expressions

At present AdapTable QL supports only standard (i.e. per-row) Expressions.

These are by far the most common type of Expression and incldes Logical Operators (AND / OR).

Hint

Future releases will include full support for all Expression types including Aggregated and Observable.

There are a huge range of Standard Expression Functions available in AQL.

These are documented fully in Adaptable For AG Grid Standard Expression Functions Guide.

Custom Expressions

AQL does not currently support Custom Expressions, i.e. bespoke Expressions provided by developers.

However this features very prominently on the roadmap and will be released very soon.

AdapTableQL in Calculated Columns

A very common use-case for AQL is Calculated Columns - the provided Expression defines the value of the column cells.

Fork Fork

In this example, the Popularity column has the following expression: [stars] > 30000 ? "HIGH": [stars] > 20000? "MEDIUM" : "LOW"
The expression calculates the popularity of the JS framework based on the number of stars in GitHub.

Open Sandbox

AdapTableQL in Filters

AdapTableQL is also used when evaluating Filters.

Expressions are used to define Column Filters...

Using AdaptableQL Expressions in Column Filters

Fork Fork

In this example, we use an Expression to define Column Filters for the stars and license columns.

filters: {
  columns: [`[stars] > 40000`, `[license] CONTAINS "MIT"`],
},

Open Sandbox

...and also when providing the Grid Filter.

Using AdaptableQL Expressions in the Grid Filter

Fork Fork

In this demo, we use an Expression to define the Grid Filter - which evalutes the stars and license columns.

filters: {
  grid: `[github_stars] > 40000 AND [license] CONTAINS "MIT"`,
},

Open Sandbox

AdapTableQL in Conditional Styles

AdapTableQL is also used when applying Conditional Styles.

Using AdaptableQL Expressions in Conditional Styles

Fork Fork

In this example, we use an Expression to define a Conditional Style on the Github Watchers column.

condition: {
  type: 'booleanExpression',
  expression: '[github_watchers] > 100 AND [github_watchers] < 500 AND [license] CONTAINS "MIT"',
},

Open Sandbox

Using Columns in AQL

Most AQL Expressions include at least one Column which is written in square brackets, e.g. [stars]

In AQL expressions, make sure you reference column ids, not the name of the data fields.

For example, if the following column is defined:

availableColumns: {
  stars: { field: 'github_stars', editable: true, dataType: 'number' },
},

Expressions should reference the column as [stars] rather than [github_stars].

Using Fields in AQL

In addition to accessing columns, AQL also supports the use of fields (e.g. FIELD("name"), FIELD("user.firstName")).

A field is a property in your row data that is not necessarily mapped to a column.

Note

When the property is an object, you can access nested properties using dot notation.

'FIELD("user.firstName"), FIELD("user.lastName")';

In a calculated column, this will look like this:

const availableColumns = {
  fullName: {
    expression: 'FIELD("user.firstName") + " " + FIELD("user.lastName")',
    dataType: 'text',
    width: 300,
  },
};

Using property access via the FIELD method in AQL

Fork Fork

In this example, the Description column is an expression that uses the name field and the language field

'FIELD("name") + ", written for " + FIELD("language")',

Open Sandbox

Fields as entities

In any AQL expression, the FIELD function can target any field/property in the row data.

However, in the UI, the user won't have access to those fields in the expression editor or the query builder unless they are defined as entities in the state.

In order to make fields available for drag-and-drop operations or for selection the in the query builder, make sure they are declared in the state.

Defining fields as entities in the state

const state: AdaptableUserState = {
  globalEntities: {
    availableColumns: { ... },
    fields: {
      'user.firstName': { // use the dot notation for nested properties
        dataType: 'text',
        label: 'First Name',
      },
      'age': {
        dataType: 'number',
        label: 'Age',
      },
    }
  }
}

Hint

To add new fields to the state, use controlled state and update your state with the new fields

PreviousUsing AdapTable components NextAdapTable State