What is the QueryAPI?

Available since 14.0 Caribou Lou release

The QueryAPI is a Groovy API that has been available in all logic since the release of version 14.0, designed to query data from the Pricefx. It was introduced to provide unified access to data within Pricefx, and, in the long term, it is expected to serve as the sole API for data queries.

The primary objective is to facilitate JOIN operations on data from two or more tables, leveraging the performance of the database engine. This approach is necessary because joining data using various techniques within Groovy logic in the backend's memory is often inefficient. Additionally, this method can reduce the number of queries made to the database.

Main Features

Supports JOIN operations across multiple tables.
Provides a single unified interface for querying data from both transactional and analytical databases.
Operates natively with field names in analytical tables and field labels in master and transactional tables.
Delivers data in a streaming format (supported in input generation mode).
Enables data aggregation.
Facilitates the use of advanced expressions.
Allows the use of column aliases to retrieve columns under different names.

The use of joins can lead to significant performance improvements; however, if not implemented correctly, they can also cause considerable issues. Therefore, a thorough understanding of database functionality is essential.

Joining data between an analytical database and a transactional database (such as in other modules) is not possible (for example, when attempting to join product extensions with a data source). Joins can only be performed between tables within the same database.

Simple Query

The primary entry point for utilizing the QueryAPI is the method api.queryApi(), which returns an interface for the QueryAPI. This interface grants access to a variety of methods necessary for executing queries.

For instance, consider the following query that retrieves a single row from the Product master table where the SKU is equal to "MB-0001":

def qapi = api.queryApi()

def p = qapi.tables().products()

return qapi.source(p, [p.sku(), p.label], p.sku().equal("MB-0001"))
        .stream { it.collect { it } }

Code Description

Line	Description

Line	Description
1	The QueryAPI reference is stored in the `qapi` variable for later use.
3	The reference to product master table is stored in `p` variable.
5	The `source()` method allows you to query the products table using a filter expression that specifies which rows should be included in the result.
6	The QueryAPI consistently returns results as a stream through the `ResultStream` interface. It is your responsibility to determine how to consume each individual row. In our example, all rows are collected into a List.

Definition (QueryAPI)

What is the QueryAPI?

Main Features

Simple Query

Code Description