Query

Understanding Queries in RestBI

A Query is the fundamental way in which you interact with your data model. It defines what data you want to retrieve, how it should be filtered, sorted, and aggregated. Understanding how to structure a query is key to extracting meaningful insights from your data.

Structure of a Query

A Query object consists of several key components, each of which plays a specific role in defining the data you want to retrieve:

Columns

An array of column names or formulas that you want to include in your result set. These can reference any column or formula defined in your model.

Example:

columns: ['Total Due', 'Order Date', 'Average Sales']

Filters

An optional array of QueryFilter objects that specify the conditions under which data should be included in the result set. Filters allow you to narrow down the results based on specific criteria.

Example:

filters: [
  { column: 'Order Date', operator: '>=', value: '2024-01-01' },
  { column: 'Customer ID', operator: 'IN', value: ['1', '2', '3'] }
]

Filters are covered in detail in the QueryFilter Documentation.

Sort By

An optional field that specifies how the result set should be ordered. It can be a single SortClause or an array of SortClause objects, allowing for multi-level sorting. Each SortClause consists of a column name and a direction (ASC or DESC) to determine the order of the rows.

Example:

sortBy: { name: 'Order Date', direction: SortDirection.ASC }

or

sortBy : [
    sortBy: { name: 'Order Date', direction: SortDirection.ASC },
    sortBy: { name: 'Customer Name', direction: SortDirection.DESC}
]

Limit

An optional number that limits the number of rows returned by the query. This is useful for controlling the size of your result set, particularly when dealing with large datasets.

Example

limit: 100

Example Query

Below is an example of a complete query that retrieves specific columns, applies filters, sorts the results, and limits the number of rows returned:

const query: Query = {
  columns: ['Total Due', 'Order Date', 'Average Sales'],
  filters: [
    { column: 'Order Date', operator: '>=', value: '2024-01-01' },
    { column: 'Customer ID', operator: 'IN', value: ['1', '2', '3'] }
  ],
  sortBy: { name: 'Order Date', direction: SortDirection.ASC },
  limit: 100
};

In this example:

  • The columns array specifies that the query will retrieve the Total Due, Order Date, and Average Sales fields.

  • The filters array ensures that only orders from 2024 onwards and those belonging to customers with IDs 1, 2, or 3 are included.

  • The sortBy field orders the results by the Order Date column.

  • The limit field restricts the results to the first 100 rows.

How the Query is Processed

When a query is executed, the system:

  1. Resolves Columns and Formulas: It determines the required tables and aliases columns accordingly.

  2. Builds SQL: The system converts the query into a SQL string by resolving column names, applying filters, and managing joins as needed.

  3. Executes the SQL: The generated SQL is sent to the database for execution, and the results are returned to the client.

See Also

PreviousQueryNextQueryFilter

Last updated