What is Query API?
Available since 14.0 Caribou Lou release
Query API is a Groovy API available in all logics since 14.0 release to query the data from Pricefx. It was introduced to provide a unified access to data in Pricefx and long term wise it should be the only API for querying data.
The main driver is to support JOIN of data from two or more tables to utilize the performance of the database engine since joining data using various techniques in groovy logic in the memory of the backend was not that efficient. It can also save the number of queries made to the database.
Main features:
Supports JOIN of tables
Provide a single unified interface for querying data for both transactional as well as analytical database
Works natively with field names (analytical tables) or field labels (master and transactional tables)
Data is provided in a stream (supported in the input generation mode)
Aggregation of the data
Advanced expressions
Allows to use column aliases to retrieve column under different name
Use of joins can be a significant performance improvement. But at the same time it can also cause a huge harm if not used properly. Therefore a knowledge of how databases work is required.
Note: Joining data between analytical database and transactional database (other modules) is not possible (e.g. a product extensions with a data source). You can join tables only within the same database.
Simple Query
The main entrance point for use of QueryAPI is a method api.queryApi() that returns a QueryAPI interface. This interface provides access to a set of methods which you will need for querying.
Let’s look at this query which reads a single row with sku = “MB-0001” from the Product master table:
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 } }
Line 1: the QueryAPI reference is stored in qapi variable for later use.
Line 3: the reference to product master table is stored in qapi variable.
Line 5: the source() method says to query products table with a filter expression that defines which rows to return in the result
Line 6: QueryAPI provides the result always as a stream (ResultStream interface). So it is up to you how you will consume each individual row. In our example, all rows are collected to a List.
Let’s look on each part in the next sections.