What is Query API?

Query API is a Groovy API available in all logics since 14.0 release to query the data from Pricefx. The aim is It was introduced to provide a unified access to data in Pricefx and idea is that ilong 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.

...

• Supports JOIN of tables

• Provide a single unified quering interface for querying data for both transacational database as well as analytics database

• Work Works natively with field labels rather than attribute numbersfield names (analytical tables) or field labels (master and transactional tables)

• Data is provided as in a stream support (which works also working in the input generation mode)

• Aggregation of the data

• Advanced expressions

• Allows to use column aliases to retrieve column under different name

Performance, Important Prerequisites

Transactional database:

Analytical database:

Simple Query

Query API provides a set of builder functions withinThe main entrance point for use of QueryAPI is a method api.queryApi(). The query is built using providing builder functions which use pipeline approach.Look at this simple query 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-00001” from the Product master table:

def q = api.queryApi()

def t1t = q.tables().productproducts()

return q.source(t1t, q.exprs().and(
                t1t.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 what how you will do with the individual rowsconsume each individual row. In this case, earch our example, all rows are collected to a List.

Let’s look how the query builder works on each part in the below next sections.

Tables

...

The main entrance point for use of QueryAPI is a method api.queryApi() that returns a QueryAPI object - object which provides access to all capabilities of the QueryAPI methods.

...

Reference to a given table is made using a method (e.g. products()) available under QueryApi.tables() method. E.g.. All those methods return Table interface. These references are used to query the desired table.

For example:

Code Block
def tableProducttables = api.queryApi().tables().product def tProduct = tables.products() def tableCoststCosts = apitables.queryApiproductExtension().tables().productExtension("Costs")

...

"Costs") def tCountries = tables.companyParameterTable("Countries")

The Table interface extends Map<String, SelectableExpression> interface. Thefore the table objects are used to reference columns.

Supported transactional tables:

• Product

• Product Extension

• Product

• Product Extension

• Seller

• SellerExentsion

• QuoteLineItems

• PriceListItem

• PriceGridITem

Analytical database tables

• Data sources

• Datamarts

Source

Expressions

• 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 suport 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 name, e.g. px.Cost, cp.Country. 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 si 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 paremeters 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.

Example:

q.source(t1, [t1.sku(), t1.Brand])

Returns sku and Brand fields from the table t1. No row filtering.

Example:

q.source(t, [t1.sku(), t1.Brand], q.exprs().and(
     t.sku().equal("MB-0001"),
))

Returns sku and Brand fields from the table t1 and filters the rows to return row with sku = “MB-0001”.

Expressions

There is quite many methods that are available, kindly refer to the Javadoc.

https://developer.pricefx.eu/pricefx-api/groovy/master/net/pricefx/formulaengine/scripting/queryapi/Expression.html

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.

...

Troubleshooting

Future vision

With the Query API, you should be able to majority of the tasks what these functions were doing:

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()

• DatamartContext.executeQuery()

• DatamartContext.executeSqlQuery()

Therefore the plan is to deprecate following methods:

...