...
Implementation
When querying data by those functions, you will commonly supply the following parameters:
typeCode - Which table you want to query. Each table (which is readable by those functions) has a Type Code.
sortBy - Name of the field, by which value you would like to sort. By default the order is ascending but if you need descending, you can prepend "-" (minus) character before the name of the column.
filters - Filters to be used for querying of the data. Those filters are of type Filter.
fields - Names of fields which you want to retrieve. If not specified, all fields of the rows are returned.
api.find()
Issues a DB query and returns one batch of records (i.e., a list of records is fetched into memory at once). The maximum size of the batch is given by server setting and you can retrieve the number by calling api.getMaxFindResultsLimit(). So even if there are more records available for your search request, you will never get more than the max limit. But you can specify which batch you want to retrieve.
Review examples below to understand the usage.
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
api.find("P", 0, api.getMaxFindResultsLimit(), "sku") //❶
api.find("P", 0, 0, "sku", ["sku", "attribute1", "attribute2"]) //❷ |
❶ Retrieves batch of records (staring from 1.record) from Product table, rows sorted by sku column. The records will have all columns.
❷ Retrieves only certain columns from the database. This is a good approach to speed up the fetch of data. The usage of 0 for max limit has the same effect as supplying the max limit (you still get capped result set).
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
def filters = [
Filter.equal("name", "SomeName"), //❶
Filter.equal("sku", sku)
]
// WARNING: Remember that api.find() returns only LIMITED amount of rows
def rows = api.find("PX", 0, api.getMaxFindResultsLimit(), null, *filters) //❷
rows.each { row -> //❸
// process the row
} |
❶ The name of the table is important, otherwise you would retrieve rows of all PX tables.
❷ When you supply only "PX" instead of "PX3", the system will find out the proper PX# based on the supplied Filter on name.
❸ Loop over the batch of records fetched into memory.
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
def filters = [
Filter.equal("name", "SomeName"),
Filter.equal("sku", sku)
]
def startRow = 0
while (rows = api.find("PX", startRow, api.getMaxFindResultsLimit(), *filters)) {
// add your processing code here
startRow += rows.size() //❶
} |
❶ Move to next batch of records.
| Tip |
|---|
Studio contains some code templates, for "find" functions, so if you need e.g., the previous piece of code then start typing "pfx", then select "pfxfind" from the dropdown list and press Enter - the editor will insert the piece of code.  |
api.namedEntities()
When the api.find() returns the result rows, the attribute# columns are called with their original names, and not the ones provided by configuration.
To convert the record maps to use the names you assigned to those attribute# columns, you can use function api.namedEntities().
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
def recordsWithAttributes = api.find("P", 0, api.getMaxFindResultsLimit(), "sku")
api.trace(recordsWithAttributes)
def recordsWithNames = api.namedEntities(recordsWithAttributes)
api.trace(recordsWithNames) |
api.stream()
Issues a DB query and opens a stream, from which you can then read one record at a time. The stream implements Java’s Iterator interface, so you can loop through the returned records one by one.
Review examples below to understand the usage.
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
def filters = [
Filter.equal("name", "SomeName"),
Filter.equal("sku", sku)
]
def iterator = api.stream("PX", null, *filters)
iterator.each { row ->
// add your row processing code here
}
iterator.close() //❶ |
❶ Always close the iterator immediately after you do not need it.
...