Vocabulary

Analytical database - Postgres database where Data sources, Datamarts, Rollups and ModelClass tables are stored.

Transactional database - Postgres database (previously MariaDB in the older infrastructure) where all other tables are stored (Master data, Company parameters, Condition records, Price lists, Quotes, Agreements, Rebates …)

What is Query API?

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

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

Data types

In transactional database, the Groovy data type for an attribute field is determined by it’s configured Type:

• String → java.lang.String

• Integer → java.lang.Long

• Real → java.math.BigDecimal

• Date → java.time.LocalDate

• Timestamp → org.joda.time.DateTime

• Boolean → java.lang.Boolean

• Link → java.lang.String

• Entity reference → java.lang.String

In analytical database, the Groovy data type for attribute field is determined by it’s configured Data Type:

• Text → java.lang.String

• Integer → java.lang.Long

• Money → java.math.BigDecimal

• Number → java.math.BigDecimal

• Date → java.time.LocalDate

• DateTime → org.joda.time.DateTime

• Boolean → java.lang.Boolean

• Currency → java.lang.String

• Quantity → java.math.BigDecimal

Source table

Source table is spcified using source() method is telling which table will be queried. If you are familiar with SQL language, 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 language. Since existing Filter class was not sufficient for the purpose of QueryAPI, a new interface Expression has been introduced.

Example:

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

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

Example:

qapi.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”.

Result stream

QueryAPI provides the result always as a stream via stream(closure) method. The closure receives ResultStream (which extends Iterator), as a parameter. So it is up to you to iterate over rows and tell how to consume each individual row. The processing of the row should be fast, because it is blocking the database connection. On the other hand collecting big amount of records to memory using collect { it } and processing it later may lead to OutOfMemory exception. So the approach should be wisely evaluated for a good compromise.

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

Adding columns from another table can be done using these methods:

• leftOuterJoin() - result contains all records from source table and matching records from the joining table

• innerJoin() - result containes only matching records from source and joining table

Right outer join method is not implemented since it can be achieved by leftOuterJoin().

The methods takes following parameters:

1. reference to a table

2. closure that returns the list of columns to be added to the result

3. closure that returns the joining condition as a boolean expression

Example:

def date = new Date()

def q = api.queryApi()

def t1 = q.tables().products()
def t2 = q.tables().productExtensionRows("Cost")

return q.source(t1, [t1.sku(), t1.ProductGroup])
        .leftOuterJoin(t2, [t2.Cost, t2.Currency],
                { cols ->
                    q.exprs().and(
                            t2.sku().equal(cols.sku),
                            t2.ValidFrom.lessOrEqual(date),
                            t2.ValidTo.greaterOrEqual(date),
                    )
                }
        )
        .stream { it.collect { it } }

The query returns all product records and their cost and currency from the product extension Cost. Or null if the product extension record does not exist.

Aggregations

Example:

def qapi = api.queryApi()

def t1 = qapi.tables().productExtension("Competition")

def exprs = qapi.exprs()

return qapi.source(t1, [t1.sku(), t1.Price])
        .aggregateBy({ cols -> [cols.sku] }, { cols -> [cols.sku, exprs.min(cols.Price).as("MinPrice")] })
        .stream().withCloseable { it.collect { it } }

Returns the sku and it’s lowest competitor’s price.

Row limit

The limit of the number of rows being returned is set by .take(maxResults) method called at the end of the pipeline and just before the stream() call.

Example:

def qapi = api.queryApi()

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

return qapi.source(p)
        .take(10)
        .stream { it.collect { it } }

Will return the first 10 rows.

Use of take() is recommended when debugging the queries.

Tracing

The QueryAPI builds a SQL (for analytical database) or HQL (for transactional database) which is then executed by a database engine. To check the final SQL/HQL query built by QueryAPI, you can call the method traceQuery() at the end of the pipeline before the stream() call.

def qapi = api.queryApi()
def p = qapi.tables().products()
return qapi.source(p)
        .traceQuery()
        .stream { it.collect { it } }

It will return the final HQL/SQL query which may help you to better understand how the data will be retrieved.

Performance, Important Prerequisites

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 as of 14.0 since they are supported by the QueryAPI:

• api.getItemCompleteCalculationResults() (performance)

• api.productExtension() (performance)

• api.customerExtension() (performance)

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