Base vocabulary
Analytical database - Postgres database where Data sources, Datamarts, Rollups and ModelClass tables are stored.
Transactional database - Postgres database (previously MariaDB in the older infrastructure) where all other tables are stored (Master data, Company parameters, Condition records, Price lists, Quotes, Agreements, Rebates …)
Tables
Reference to a given table is made using a method (e.g. products()) available under QueryApi.tables(). All those methods return Table interface. These references are used to query the desired table.
For example:
def tables = api.queryApi().tables()
def tProduct = tables.products()
def tCosts = tables.productExtension("Costs")
def tCountries = tables.companyParameterTable("Countries")
The table objects are used to reference columns.
Supported transactional tables:
Product
Product extension
Customer
Customer extension
Seller
Seller extension
Company parameters
Condition records
Pricelist items
Price grid items
Calculation grid items
Quote line items
Contract line items
Rebate line items
Rebate records
Compensation line items
Compensation records
Supported analytical tables:
Analytical tables are currently not supported, the plan is to support these in 15.0 release:
Data sources
Datamarts
Rollups
Model Class Tables
Columns
Columns are fields referenced from table objects. For transactional tables, those can be:
Attribute fields
System fields
Attribute fields can be used in the QueryAPI only if you configure a name and data type for that attribute field. Attribute fields are accessed as a property by the configured label, e.g. px.Cost, cp.Country. Therefore it is not recommended to configure attribute labels with spaces, since it makes the code worse readable, however such column names are supported. Attribute fields cannot be accessed by their system field name, e.g. p.attribute3 will not work.
System fields can be accessed using methods in the given table interface, e.g. p.sku(), c.customerId(), p.lastUpdateDate().
If you want to retrieve the column under a different name, you can use an alias method as(), eg.:
px.Cost.as("StandardCost")p.sku().as("Product")
Data types
In transactional database, the Groovy data type for an attribute field is determined by it’s configured Type:
String → java.lang.String
Integer → java.lang.Long
Real → java.math.BigDecimal
Date → java.time.LocalDate
Timestamp → org.joda.time.DateTime
Boolean → java.lang.Boolean
Link → java.lang.String
Entity reference → java.lang.String
QueryAPI converts the value using CAST in SQL. So if there is an issue with the data integrity (e.g. integration loaded a string value in an attribute field of type Integer) then the whole query call crashes.
Unity stores empty values of attribute fields in transactional database as an empty string. Due to the CAST above, QueryAPI performs a special CASE-WHEN check for empty string values in order to not cause crashes for empty values. E.g.:
CASE WHEN attribute1 = '' THEN NULL ELSE CAST(attribute1 AS LocalDate)END
This check cause index that has attribute1not to be used.
The check is omitted if the attribute field is marked with Required flag. The expression will be come simple cast:
CAST(attribute1 AS LocalDate)
So if you can it is recommended to mark attribute fields with Required flag whenever possible. But it has to be ensured (e.g. in integration or by boundCall) that a value cannot be empty.
In analytical database, the Groovy data type for attribute field is determined by it’s configured Data Type:
Text → java.lang.String
Integer → java.lang.Long
Money → java.math.BigDecimal
Number → java.math.BigDecimal
Date → java.time.LocalDate
DateTime → org.joda.time.DateTime
Boolean → java.lang.Boolean
Currency → java.lang.String
Quantity → java.math.BigDecimal
Source table
Source table is spcified using source() method is telling which table will be queried. If you are familiar with SQL language, you can look at this method as “FROM” clause in SQL.
The first parameter is a mandatory one and accepts a reference to a table, the other parameters are optional. The second parameter specifies the list of columns to be returned. The third parameter is an expression that defines filter on the returned rows. So it is like a “WHERE” clause in SQL language. Since existing Filter class was not sufficient for the purpose of QueryAPI, a new interface Expression has been introduced.
Example:
qapi.source(t1, [t1.sku(), t1.Brand])
Returns sku and Brand fields from the table t1. No row filtering.
Example:
qapi.source(t1, [t1.sku(), t1.Brand],
t1.sku().equal("MB-0001")
)
Returns sku and Brand fields from the table t1 and filters the rows to return row with sku = “MB-0001”.
Result stream
QueryAPI provides the result always as a stream via stream(closure) method. The closure receives ResultStream (which extends Iterator), as a parameter. So it is up to you to iterate over rows and tell how to consume each individual row. The processing of the row should be fast, because it is blocking the database connection. On the other hand collecting big amount of records to memory using collect { it } and processing it later may lead to OutOfMemory exception. So the approach should be wisely evaluated for a good compromise.
Expressions
There are quite many methods available, please refer to the Javadoc.
Joining tables
Adding columns from another table is done using these methods:
leftOuterJoin()- result contains all records from source table and matching records from the joining tableinnerJoin()- result containes only matching records from source and joining table
Right outer join method is not implemented since the same effect can be done usingleftOuterJoin().
Multiple joins can be done.
The methods takes following parameters:
reference to a table
closure that returns the list of columns to be added to the result
closure that returns the joining condition as a boolean expression
It is strongly recommended that the joining conditions use all fields of the database primary index.
Example:
def date = new Date()
def q = api.queryApi()
def t1 = q.tables().products()
def t2 = q.tables().productExtensionRows("Cost")
return q.source(t1, [t1.sku(), t1.ProductGroup])
.leftOuterJoin(t2, [t2.Cost, t2.Currency],
{ cols ->
q.exprs().and(
t2.sku().equal(cols.sku),
t2.ValidFrom.lessOrEqual(date),
t2.ValidTo.greaterOrEqual(date),
)
}
)
.stream { it.collect { it } }
The query returns all product records and their cost and currency from the product extension Cost. Or null for cost and currency if the product extension record does not exist.
Removing columns
If you had to add columns via source(), leftOuterJoin or innerJoin in order to make the joining condition but you do not need those in the result, you can do this using removeColumns() method. The method takes a closure as the argument and should return a list of columns to be removed.
Example:
.removeColumns { cols -> [cols.ValidFrom, cols.ValidTo] }
Retaining columns
If you had to add columns via source(), leftOuterJoin or innerJoin in order to make the joining condition but you do not need those in the result, you can do this using retainColumns() method. The method takes a closure as the argument and should return a list of columns that should be present in the result.
Example:
.retainColumns { cols -> [cols.sku, cols.Cost] }
Additional filtering
If you made several joins using leftOuterJoin or innerJoin you can apply additional filtering of the result using filter() method. The method takes a closure as the argument and should return an expression which if valuated to true, telss if the row should be present in the result.
Example:
.filter { cols -> cols.Cost.isNotNull() }
Aggregations
Example:
def qapi = api.queryApi()
def t1 = qapi.tables().productExtension("Competition")
def exprs = qapi.exprs()
return qapi.source(t1, [t1.sku(), t1.jm Price])
.aggregateBy({ cols -> [cols.sku] }, { cols -> [cols.sku, exprs.min(cols.Price).as("MinPrice")] })
.stream { it.collect { it } }
Returns lowest competitor’s price for each SKU.
Sorting
To sort the result rows, use a sortBy() method. This method takes a closure as an argument and should return a list of orders. The orders can be one of the following methods, depending whether the column values should be sorted in ascending or descdending order and if there are null values whether those should come first or last:
Orders.ascNullsFirst()Orders.ascNullsLast()Orders.descNullsFirst()Orders.descNullsLast()
Example:
def qapi = api.queryApi()
def t1 = qapi.tables().companyParameterRows("ProductHierarchy")
def orders = qapi.orders()
return qapi.source(t1, [t1.ProductGroup, t1.ProductLine])
.sortBy { cols -> [orders.ascNullsLast(cols.ProductGroup), orders.ascNullsLast(cols.ProductLine)] }
.stream { it.collect { it } }
Will sort the result rows by ProductGroup and then by ProductLine.
Row limit
The limit of the number of rows being returned is set by take(maxResults) method called at the end of the pipeline and just before the stream() call.
Example:
def qapi = api.queryApi()
def p = qapi.tables().products()
return qapi.source(p)
.take(10)
.stream { it.collect { it } }
Will return the first 10 rows.
Use of take() is recommended when debugging the queries.
Tracing
The QueryAPI builds a SQL (for analytical database) or HQL (for transactional database) which is then executed by a database engine. To check the final SQL/HQL query built by QueryAPI, you can call the method traceQuery() at the end of the pipeline before the stream() call.
def qapi = api.queryApi()
def p = qapi.tables().products()
return qapi.source(p)
.traceQuery()
.stream { it.collect { it } }
It will return the final HQL/SQL query which may help you to better understand how the data will be retrieved.