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
Use as such:
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
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
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
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
See the source code of Competition.groovy.
Found an issue in documentation? Write to us.