***************************************************** ***************************************************** ***************************************************** ***************************************************** ***************************************************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** This Confluence article was automatically generated from Asciidoc. Any changes you make to this document will be overridden! If you want to change the content, consider leaving a comment. You can edit the content directly here: https://gitlab.pricefx.eu/training/pricefx-knowledge-base/-/tree/dev/public/content/docs/quick-reference/data-queries/general-queries ***************************************************** ***************************************************** ***************************************************** ***************************************************** ***************************************************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING ********************** ********************** WARNING **********************
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. |
api.find()
is useful when you want to get only a single result. In that case, specify 1
as the maxRows
argument.
Map<String, Object> findProduct(String sku){ def filter = Filter.equal('sku', sku) return api.find('P', 0, 1, null, null, filter)?.getAt(0) } |
Results from api.find()
are paginated. Thus you can use api.find()
to iterate even a large data set.
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 } |
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. |
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 } |
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()
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"]) |
You can retrieve an aggregated value of a field – the values of filtered rows are grouped together to form a single aggregated value.
You can calculate aggregates with the SUM
SQL function in api.find()
rather than iterating over the collection.
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.
You can calculate averages with the AVG
SQL function in api.find()
rather than iterating over the collection.
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.
You can calculate minimum and maximum values with the MIN
and MAX
SQL functions in api.find()
rather than iterating over the collection.
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.