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 analytics database
Works natively with field names (analytical tables) or field labels (master and transactional tables)
Data is provided in a stream (working 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 (PA module) 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 t = qapi.tables().products()
return q.source(t, t.sku().equal("MB-0001"))
.stream { it.collect { it } }
Line 1: the QueryAPI reference is stored in q variable for later use.
Line 3: the reference to product master table is stored in q variable.
Line 5: the source() method says to query table t
Line 5-8: filter expression that defines which rows to return in the result
Line 8: 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.
Tables
Reference to a given table is made using a method (e.g. products()) available under QueryApi.tables(). All those methods return Table interface. These references are used to query the desired table.
For example:
def tables = api.queryApi().tables()
def tProduct = tables.products()
def tCosts = tables.productExtension("Costs")
def tCountries = tables.companyParameterTable("Countries")
The Table interface extends Map<String, SelectableExpression> interface. Therefore the table objects are used to reference columns.
Supported transactional tables:
Product
Product extension
Customer
Customer extension
Seller
Seller extension
Company parameters
Condition records
Pricelist items
Price grid items
Calculation grid items
Quote line items
Contract line items
Rebate line items
Rebate records
Compensation line items
Compensation records
Supported analytical tables:
Analytical tables are currently not supported, the plan is to support these in 15.0 release:
Data sources
Datamarts
Rollups
Model Class Tables
Columns
Columns are fields referenced from table objects. For master or transactional tables, those can be:
Attribute fields
System fields
Attribute fields can be used in the QueryAPI only if you configure a name and data type for that attribute field. Attribute fields are accessed as a Map property by the configured label, e.g. px.Cost, cp.Country. Therefore it is not recommended to configure attribute labels with spaces, since it makes the code worse readable, however such column names are supported. Attribute fields cannot be accessed by their system field name, e.g. p.attribute3 will not work.
System fields can be accessed as Map properties as well, but also using methods in the given table interface, e.g. p.sku(), c.customerId(), p.lastUpdateDate(). It is recommended to use the provided methods, since it will help you when finding column references in the source code.
Columns are instances of SelectableExpression interface.
If you want to retrieve the column under a different name, you can use an alias method .as(), eg.:
px.Cost.as("StandardCost")p.sku().as("Product")
Source
Source function is telling which table will be queried. If you are familiar with SQL, you can look at this method as “FROM” clause in SQL.
The first parameter is a mandatory one and accepts a reference to a table, the other parameters are optional. The second parameter specifies the list of columns to be returned. The third parameter is an expression that defines filter on the returned rows. So it is like a “WHERE” clause in SQL. Since existing Filter class was not sufficient for the purpose of QueryAPI, a new interface Expression has been introduced.
Example:
q.source(t1)
Returns all fields from table t1. No row filtering. Note that production code you should specify the fields in order to only return fields really used due to performance. So preferably use this:
Example:
q.source(t1, [t1.sku(), t1.Brand])
Returns sku and Brand fields from the table t1. No row filtering.
Example:
q.source(t1, [t1.sku(), t1.Brand],
t1.sku().equal("MB-0001")
)
Returns sku and Brand fields from the table t1 and filters the rows to return row with sku = “MB-0001”.
Stream
QueryAPI provides the result always as a stream (ResultStream interface). So it is up to you how you will consume each individual row. The processing of the row should be fast, because it is holding a database connection. On the other hand collecting big amount of records to memory using .collect { it } may lead to OutOfMemory exception. So this needs to be wisely
Expressions
There is quite many methods that are available, kindly refer to the Javadoc.
Joining tables
It is strongly recommended that you make joins only based on index fields in the join criteria.
Aggregations
Tracing
If you want to check the result query being executed by the database engine, you can call a following method call at the end of the pipeline and just before the stream part:
.traceQuery()
It will return the final HQL query.
Performance, Important Prerequisites
Master and transactional tables:
Analytical tables:
Studio support
REST API
Currently, thre is no REST API for queries using QueryAPI. But you can create a logic that returns the data using QueryAPI since every generic logic can be executed using formulamanager.execute endpoint.
See more Custom REST API Service Using Logics
Troubleshooting
Future vision
Following methods are deprecated since they are better supported by the QueryAPI.
api.getItemCompleteCalculationResults()api.productExtension()api.customerExtension()
More tables will be supported by the QueryAPI in the future. Therefore these methods that may be deprecated in the future:
DatamartContext.executeQuery()DatamartContext.executeSqlQuery()api.find()api.findLookupTable()api.findLookupTableValues()api.priceGrids()api.pricelists()api.findApprovedPricelistItems()api.findDataload()api.findCalculatedFieldSets()