General Queries (Quick Reference)

Pricefx provides two methods for querying almost any kind of data – api.find() and api.stream().

Both methods let you specify a logical condition that the results will be matched against, the order in which the data will be retrieved, and the fields that will be included in the result.

api.find() returns a list of results. Because the set of matching results can potentially be very large, api.find() has a limit for the size of the returned array. Therefore, api.find() is mostly useful when you are certain that the matching data set is small – for example when you want find the most recent price of a certain product.

api.stream() returns an iterator that allows you to iterate the full collection. However, it opens a JDBC collection that should be open for as short amount of time as possible.

The methods api.find() and api.stream() do not work for Data Sources, Datamarts, and Data Feeds.

Find a Single Result

api.find() is useful when you want to get only a single result. In that case, specify 1 as the maxRows argument.

Product.groovy

Map<String, Object> findProduct(String sku){
    def filter = Filter.equal('sku', sku)
    return api.find('P', 0, 1, null, null, filter)?.getAt(0)
}

Paginated Results

Results from api.find() are paginated. Thus you can use api.find() to iterate even a large data set.

Pagination.groovy

import com.googlecode.genericdao.search.Filter
import groovy.transform.Field

@Field final Integer MAX_ROWS = 10

void forEach(String typeCode, Filter filter, Closure callback) {
    int startRow = 0
    List results
    while(results = api.find(typeCode, startRow, MAX_ROWS, null, null, filter)){
        startRow += results.size()
        results.forEach(callback)
    }
}

And use as such:

def pagination = libs.Library_Queries.Pagination
pagination.forEach(typeCode, filter ) { row ->
    // Do something with row
}

Streaming Large Result Sets

Iterating larger data sets is easier with api.stream(), as it does not require that you deal with pagination. However, it requires that you close the stream afterwards as soon as possible.

The interface for api.stream() does not have a proper implementation when the logic is executed in the input generation mode.

PriceList.groovy

void forEachPriceListItem(Filter priceListFilter, Closure callback){
    // The price list item type does not store the target date for the price list
    api.stream("PL", null, priceListFilter).each { priceList ->
        def targetDate = priceList.targetDate
        def priceListId = priceList.id
        def filter = Filter.equal('pricelistId', priceListId)
        api.stream("PLI", null, filter).each { pricelistItem ->
            callback(priceList, pricelistItem)
        }.close()
    }.close()
}

Use as such:

def priceListUtils = libs.Library_Queries.PriceList
def filter = Filter.equal('approvalState', 'APPROVED')
priceListUtils.forEachPriceListItem(filter) { priceList, priceListItem ->
    // Do something with priceList and priceListItem
}

Select Fields to Retrieve

If not specified, api.find() and api.stream() will return all the available fields from the records. But that is not recommended, because typically you know exactly which of the fields you need for your calculations. So as good practice, and also to improve performance, retrieve only the fields which you really need.

You can also directly extract fields from JSON data which is handy for values from inputs and outputs fields. This works for both api.find() and api.stream()

Samples of calls using the fields projection

api.find("Q",0, 0, null, ["uniqueName", "inputs.Customer.value"])

api.stream("Q", null, ["uniqueName", "inputs.Customer.value"])



api.find("QLI",0, 0, null, ["clicId", "sku",
                            "inputs.Quantity.value", "outputs.Cost.result"])

api.stream("QLI", null, ["clicId", "sku",
                            "inputs.Quantity.value", "outputs.Cost.result"])

Aggregate Functions

You can retrieve an aggregated value of a field – the values of filtered rows are grouped together to form a single aggregated value.

Summary

You can calculate aggregates with the SUM SQL function in api.find() rather than iterating over the collection.

Sales.groovy

BigDecimal getTotalSales(
        Date fromDate,
        Date toDate = new Date()
) {
    String fromString = fromDate.format('yyyy-MM-dd')
    String toString = toDate.format('yyyy-MM-dd')
    def filter = Filter.and(
            Filter.greaterOrEqual('validAfter', fromDate),
            Filter.lessOrEqual('validAfter', toDate)
    )
    api.find('PR', 0, 1, null, [attribute1: 'SUM'], false, filter).getAt(0)?.attribute1
}

See the source code of Sales.groovy.

Average

You can calculate averages with the AVG SQL function in api.find() rather than iterating over the collection.

Competition.groovy

BigDecimal getAverageCompetitionPrice(String sku, String countryCode){
    Filter filter = Filter.and(
            Filter.equal('sku', sku),
            Filter.equal('country', countryCode)
    )
    api.find('PCOMP', 0, 1, null, [price: "AVG"], false, filter).getAt(0)?.price
}

See the source code of Competition.groovy.

Minimum & Maximum

You can calculate minimum and maximum values with the MIN and MAX SQL functions in api.find() rather than iterating over the collection.

Competition.groovy

BigDecimal getMaxCompetitionPrice(String sku, String countryCode){
    Filter filter = Filter.and(
            Filter.equal('sku', sku),
            Filter.equal('country', countryCode)
    )
    api.find('PCOMP', 0, 1, null, [price: "MAX"], false, filter).getAt(0)?.price
}

BigDecimal getMinCompetitionPrice(String sku, String countryCode){
    Filter filter = Filter.and(
            Filter.equal('sku', sku),
            Filter.equal('country', countryCode)
    )
    api.find('PCOMP', 0, 1, null, [price: "MIN"], false, filter).getAt(0)?.price
}

See the source code of Competition.groovy.