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 to provide a unified access to data in Pricefx and idea is that it should be the only API.

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 quering interface for transacational database as well

• Work natively with field labels rather than attribute numbers

• Data is provided as a stream support (which works also 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 withinapi.queryApi(). The query is built using providing builder functions which use pipeline approach.

Look at this simple query:

def q = api.queryApi()

def t1 = q.tables().product()

return q.source(t1, q.exprs().and(
                t1.sku().equal("MB-0001"),
        ))
        .stream { it.collect { it } }

It that takes reads a single table row with sku “MB-00001” from the Product table. Streaming is used in QueryAPI as the only option and it is up to you what you will do with the individual rows. In this case, earch are collected to a List.

Let’s look how the query builder works in the below sections.

Tables And Columns

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 table is retrieved using the QueryApi.tables() method. E.g.

def tableProduct = api.queryApi().tables().product()
def tableCosts = api.queryApi().tables().productExtension("Costs")

Transactional database tables supported:

• Product

• Product Extension

• Product

• Product Extension

• Seller

• SellerExentsion

• QuoteLineItems

• PriceListItem

• PriceGridITem

Analytical database tables

• Data sources

• Datamarts

Source

Expressions

Joining tables

It is strongly recommended that you make joins only based on index fields in the join criteria.

Aggregations

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

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

• 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:

More functions will be added here once they are covered by the QueryAPI.