...

def qapi = api.queryApi()

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

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

...

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

...

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:

qqapi.source(t1, [t1.sku(), t1.Brand])

Returns sku and Brand fields from the table t1. No row filtering.

Example:

qqapi.source(t1, [t1.sku(), t1.Brand], 
    t1.sku().equal("MB-0001")
)

...

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.

...

Adding columns from another table can be done using these methods:

• .leftOuterJoin(). - result contains all records from source table and matching records from the joining table

• innerJoin() - result containes only matching records from source and joining table

Right outer join method is not implemented since it can be achieved by leftOuterJoin().

The methods takes following parameters:

1. reference to a table

2. closure returning columns added

3. closure returning

...

1. that returns the list of columns to be added to the result

2. closure that returns the joining condition as a boolean expression

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, { cols -> [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 } }

add

It is strongly recommended that you make joins only based on index fields in the join criteriaThe 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

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.

...

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.

...