...

Let’s look at this query which reads a single row with sku = “MB-0001” from the Product master table:

def qapi = api.queryApi()

def p = qapi.tables().products()

return qapi.source(p, p.sku().equal("MB-0001"))
        .stream { it.collect { it } }

...

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

...

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 if the product extension record does not exist.

Aggregations

Example:

def qapi = api.queryApi()

def t1 = qapi.tables().productExtension("Competition")

def exprs = qapi.exprs()

return qapi.source(t1, [t1.sku(), t1.Price])
        .aggregateBy({ cols -> [cols.sku] }, { cols -> [cols.sku, exprs.min(cols.Price).as("MinPrice")] })
        .stream().withCloseable { it.collect { it } }

Returns the sku and it’s lowest competitor’s price.

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 } }

...

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 } }

...