General Data Queries

Owned by Maroš Grman
Last updated: Sept 20, 2024 by Ioana Kocurova
8 min read

Almost any kind of Logic in any module needs to read data from tables and then uses them for calculation. In this article we will describe mainly the "generic" functions, how you can read data from almost any table stored in MariaDB database - i.e., Master Data, Pricelists, …​. (generally speaking almost everything with the exception of Analytics data).

Concept

The Groovy API has many functions for retrieving data. Most of them are specific for certain type of tables, and only a few are for general purpose.

Specific Retrieval Functions

Many tables have associated specific functions in the Groovy API, which are dedicated for reading from them, e.g.:

  • Product/Customer Master

    • api.product()

    • api.customer()

  • Product/Customer Extensions

    • api.productExtension()

    • api.customerExtension()

  • Company Parameter tables

    • api.vLookup()

    • api.findLookupTable()

    • api.findLookupTableValues()

  • Pricelist

    • api.pricelist()

  • etc.

For extensive list, please see Read / Lookup / Search / Find Functions

General Purpose Functions

There are two functions, which can query/read all the tables, which have a Type Code associated:

  • api.find(…​)

  • api.stream(…​)

The main difference between them is the principle, how they fetch the data. Which of the approaches you will use, depends on the use-case you’re solving:

  • 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 would be 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.

  • 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.

These two functions are commonly used across all modules.

Data Model

List of Tables, Their TypeCodes and Fields

The detailed list of fields is available in Studio, in the tool PfxMetadata. As the list if quite long, you can also filter it by name of table or attribute.

Figure 1. Sample of searching for specific table in PfxMetadata tool available in Studio.

In the screenshot you can see that data of the Company Parameter "MarginAdj" are stored in table with TypeCode "LTV".

Table Relations

When you lookup some tables, it’s handy to realize the relationships between the tables.

The diagrams presented below do not have referential integrity enforced by database.

Figure 2. Product Master and its related tables

Attribute Columns

Many master data tables (e.g., Product, ProductExtension, Matrix PriceParameter, …​) have configurable attribute columns. You cannot add or remove the attribute columns but you can "Rename and Customize" such columns, specifically you can define for them:

  • Name - In the query to such table, when you need to refer to the column, you can use this "Name" instead of the generic column name "attribute1". Note: If you wanted to find this property in JSON metadata, the name is stored under property "label".

  • Label - What the user will see on the screen. This is implemented as "translations", so you can provide more names for different languages.

  • Data Type - How the content of the column will be provided to the logic. The physical storage of attribute# columns in the database is String but when queried and the result is provided to the logic, it’s converted to this specified type.

Product/Customer Extensions

There are several types of Product/Customer Extension tables with different numbers - 3, 6, 8, etc. This number says, how many attribute columns those tables have. The TypeCodes of those tables is then PX3, PX6, PX8, etc.

When querying the PX# tables using the generic functions, it’s important to realize, that data rows of all PX# tables (with the same number) will be stored in the same database table. So the query to PX# must always contain a filter for the name of the table to distinguish, which rows you want to retrieve.

Company Parameters

There are several types of tables, which store rows of various types of the Company Parameters:

  • LTV - Rows of all Simple Company Parameters tables.

  • MLTV - Rows of all Matrix (i.e., Matrix#1) Company Parameters tables.

  • MLTV1..6 - Rows of all Matrix# Company Parameters tables.

When querying the Company Parameter tables using the generic functions, it’s important to realize that data rows of all e.g., "Matrix(1)" tables are stored in the same MLTV table. So then constructing query, ensure you filter by ID of the Company Parameter table. Refer to sections about Table Relations for the relations between the LT and MLTV tables.

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.

Basic query to Product Master table

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).

Query PX table with name "SomeName" - using Filters

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.

Loop through all records in batches

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.

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().

Sample of usage of api.namedEntities()`

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.

Read through all records of PX table "SomeName" for specific given SKU

❶ Always close the iterator immediately after you do not need it.

Considerations

Possible important considerations which you must bear in mind.

Performance

When you read data, you should always read the minimum of:

  • Rows - Use Filters, do not fetch unnecessary lines.

  • Columns - Tell the function that you want only certain columns back.

Short Loop when Reading api.stream()

The loop for reading data from api.stream() iterator should be as quick as possible, because the connection to database is opened all the time.

If you need to do some time-consuming processing with the data, then consider using the api.find() loop, because then you’re doing more queries, but the query returns batch of records into memory and you can process them without being still blocking the connection.

Summary

The Analytics query is set of functions from Pricefx Groovy API. Similar to SQL in capabilities, enabling us to read/query data from Analytics tables like Datamart, DataSource or Rollup and also from Optimizer’s Model table. Analytics data tables are stored in PostgreSQL database, which is suitable for large dataset queries.

There are two general purpose functions which allow us to query data from all the tables that have a Type Code associated, the api.find(…​) and api.stream(…​). These two functions are commonly used across all modules.

  • api.find() issues a DB query and returns one batch of records (i.e., a list of records is fetched into memory at once).

  • api.stream() issues a DB query, and opens a stream from which you can then read one record at a time.

Question to think about: Think of two scenarios where you would use api.stream() and two scenarios where you would use api.find(). Would it be possible to use specific retrieval functions in these scenarios instead?

References

Groovy API

Documentation

Knowledge Base

Found an issue in documentation? Write to us.