...

Base vocabulary

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

...

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 are quite many methods that are available, kindly please refer to the Javadoc.

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

...

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

...

Right outer join method is not implemented since it the same effect can be achieved by done usingleftOuterJoin().

Multiple joins can be done.

The methods takes following parameters:

...

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

Aggregations

Example:

...

...

Removing columns

If you had to add columns via source(), leftOuterJoin or innerJoin in order to make the joining condition but you do not need those in the result, you can do this using removeColumns() method. The method takes a closure as the argument and should return a list of columns to be removed.

Example:

.removeColumns { cols -> [cols.ValidFrom, cols.ValidTo] }

Retaining columns

If you had to add columns via source(), leftOuterJoin or innerJoin in order to make the joining condition but you do not need those in the result, you can do this using retainColumns() method. The method takes a closure as the argument and should return a list of columns that should be present in the result.

Example:

.retainColumns { cols -> [cols.sku, cols.Cost] }

Additional filtering

If you made several joins using leftOuterJoin or innerJoin you can apply additional filtering of the result using filter() method. The method takes a closure as the argument and should return an expression which if valuated to true, telss if the row should be present in the result.

Example:

.filter { cols -> cols.Cost.isNotNull() }

Aggregations

Example:

def qapi = api.queryApi()

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

def exprs = qapi.exprs()

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

Returns lowest competitor’s price for each SKU.

Sorting

To sort the result rows is done using sortBy() call. The method takes a closure as an argument. The closure should return a list of orders. The orders can be one of the following:

• Orders.ascNullsFirst()

• Orders.ascNullsLast()

• Orders.descNullsFirst()

• Orders.descNullsLast()

Example:

def qapi = api.queryApi()

def t1 = qapi.tables().productExtensioncompanyParameterRows("CompetitionProductHierarchy")

def exprsorders = qapi.exprsorders()

return qapi.source(t1, [t1.sku()ProductGroup, t1.jm Price])
 ProductLine])
      .aggregateBy({ cols -> [cols.sku] }, .sortBy { cols -> [orders.ascNullsLast(cols.skuProductGroup), exprsorders.minascNullsLast(cols.Price).as("MinPrice"ProductLine)] })
        .stream { it.collect { it } }

Returns lowest competitor’s price for each SKUWill sort the result rows by ProductGroup and then by ProductLine.

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.

...