Report Static Query Builder

Overview

The MiniQueryBuilder is a fluent interface for building SQL-like queries in Gaio Data OS. It provides a simple and intuitive way to construct queries with various clauses like SELECT, FROM, WHERE, GROUP BY, ORDER BY, LIMIT, and OFFSET.

Basic Usage

// Access the query builder through context.query
const results = await context.query
  .select(['id', 'name', 'created_at'])
  .from('users')
  .filter('status', 'eq', 'active')
  .orderBy('created_at', 'desc')
  .limit(100)
  .run();

API Reference

Constructor

The query builder is accessed through the context object:

// You don't need to create an instance - use context.query
const query = context.query;

Methods

select(fields: string[])

Specifies the fields to select in the query.

context.query.select(['id', 'name', 'email']);

from(table: string)

Specifies the table to query from. This method is required.

context.query.from('users');

filter(field: string, operator: FilterOperator, value: any)

Adds a filter condition to the query.

context.query.filter('age', 'gt', 18);

Available operators:

• eq - equals

• neq - not equals

• gt - greater than

• gte - greater than or equal

• lt - less than

• lte - less than or equal

• like - pattern matching with %

• ilike - case insensitive pattern matching

• in - in a list of values

• is - checking for null

• cs - contains (for arrays)

• cd - contained by (for arrays)

• ov - overlap (for arrays)

• fts - full text search

• or - logical OR

• and - logical AND

groupBy(fields: string[])

Specifies fields to group by.

context.query.groupBy(['department', 'role']);

orderBy(field: string, direction: 'asc' | 'desc' = 'asc', nullsPosition?: 'first' | 'last')

Adds an order by clause to the query.

context.query.orderBy('created_at', 'desc', 'last');

limit(limit: number)

Sets the maximum number of rows to return.

context.query.limit(100);

offset(offset: number)

Sets the number of rows to skip.

context.query.offset(50);

run(): Promise<GenericType[]>

Executes the query and returns a Promise that resolves to an array of results. This is the primary method for executing the query and retrieving data.

Usage

const results = await context.query
  .select(['id', 'name'])
  .from('users')
  .limit(100)
  .run();

Return Value

Returns a Promise that resolves to an array of objects (GenericType[]) representing the query results. Each object in the array corresponds to a row in the result set, with properties matching the selected columns.

Validation

The run() method performs the following validations before executing the query:

• Ensures that the limit is a number

• Ensures that the limit does not exceed 10,000 rows

• Implicitly checks that the FROM clause is specified (through the build method)

Error Handling

The run() method may throw errors in the following cases:

• If the limit is not a number: Error('Limit must be a number')

• If the limit exceeds 10,000: Error('Limit must be less than 10000')

• If the FROM clause is not specified: Error('FROM clause is required. Use .from() to specify a table.')

• If the API request fails: The error from the API client will be propagated

Implementation Details

The run() method sends a POST request to api/table/query with the query schema and task data. It then extracts and returns the data from the response.

Examples

Basic Query

const users = await context.query
  .select(['id', 'name', 'email'])
  .from('users')
  .limit(100)
  .run();

Filtered Query

const activeUsers = await context.query
  .select(['id', 'name', 'email'])
  .from('users')
  .filter('status', 'eq', 'active')
  .run();

Aggregation Query

const departmentCounts = await context.query
  .select(['department', 'COUNT(*) as count'])
  .from('employees')
  .groupBy(['department'])
  .orderBy('count', 'desc')
  .run();

Pagination

const page = 2;
const pageSize = 50;

const paginatedUsers = await context.query
  .select(['id', 'name', 'email'])
  .from('users')
  .orderBy('id', 'asc')
  .limit(pageSize)
  .offset((page - 1) * pageSize)
  .run();