QBSelect is a wrapper for building SQL SELECT queries in Servoy, providing a flexible API to add conditions, sorting, grouping, joins, and parameters to SQL-based queries. Through structured access to clauses like where, groupBy, and joins, QBSelect supports complex query construction and parameterized queries.
For detailed query building, see Query Builder in the Servoy documentation.
TypeQBCase a case expression for conditional logic in the query.
Sample
var query = datasources.db.example_data.order_details.createSelect();
// case expressions can be added to the result of the query
query.result.add(query.case.when(query.columns.quantity.ge(1000)).then('BIG').else('small'));
// they can also be used in conditions
query.where.add(query.case
.when(query.columns.discount.gt(10)).then(50)
.when(query.columns.quantity.le(20)).then(70)
.else(100)
.multiply(query.columns.unitprice).lt(10000));
columns
Get all the columns of the datasource that can be used for this query (select or where clause)
var query = datasources.db.example_data.orders.createSelect();
query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries
.root.having.add(query.joins.orders_to_order_details.columns.quantity.count.eq(0))
foundset.loadRecords(query)
having
Get the having-part of the query, used to add conditions.
The conditions added here are AND-ed.
TypeQBLogicalCondition the having-part of the query for adding conditions.
Sample
var query = datasources.db.example_data.orders.createSelect();
query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries
.root.having.add(query.joins.orders_to_order_details.columns.quantity.count.eq(0))
foundset.loadRecords(query)
joins
Get the joins clause of this table based clause.
Joins added to this clause will be based on this table clauses table.
Returns:QBColumn the QBColumn representing the specified column from the table with the given alias.
Sample
foundset.getQuery().getColumn('orderid', 'opk')
getDataSet(max_returned_rows)
Performs a sql query with a query builder object. Same as databaseManager.getDataSetByQuery.
Will throw an exception if anything did go wrong when executing the query.
Using this variation of getDataSet any Tablefilter on the involved tables will be taken into account.
Parameters
Numbermax_returned_rows The maximum number of rows returned by the query.
Returns:JSDataSet The JSDataSet containing the results of the query.
Sample
// use the query from foundset and add a condition
/** @type {QBSelect<db:/example_data/orders>} */
var q = foundset.getQuery()
q.where.add(q.joins.orders_to_order_details.columns.discount.eq(2))
var maxReturnedRows = 10;//useful to limit number of rows
var ds = q.getDataSet( maxReturnedRows);
// query: select PK from example.book_nodes where parent = 111 and(note_date is null or note_date > now)
var query = datasources.db.example_data.book_nodes.createSelect().result.addPk().root
query.where.add(query.columns.parent_id.eq(111))
.add(query.or
.add(query.columns.note_date.isNull)
.add(query.columns.note_date.gt(new Date())))
query.getDataSet(max_returned_rows)
getDataSet(max_returned_rows, useTableFilters)
Performs a sql query with a query builder object. Same as databaseManager.getDataSetByQuery.
Will throw an exception if anything did go wrong when executing the query.
Parameters
Numbermax_returned_rows The maximum number of rows returned by the query.
BooleanuseTableFilters use table filters (default true).
Returns:JSDataSet The JSDataSet containing the results of the query.
Sample
// use the query from a foundset and add a condition
/** @type {QBSelect<db:/example_data/orders>} */
var q = foundset.getQuery()
q.where.add(q.joins.orders_to_order_details.columns.discount.eq(2))
var maxReturnedRows = 10;//useful to limit number of rows
var ds = q.getDataSet(true, maxReturnedRows);
// query: select PK from example.book_nodes where parent = 111 and(note_date is null or note_date > now)
var query = datasources.db.example_data.book_nodes.createSelect().result.addPk().root
query.where.add(query.columns.parent_id.eq(111))
.add(query.or
.add(query.columns.note_date.isNull)
.add(query.columns.note_date.gt(new Date())))
query.getDataSet(true, max_returned_rows)
Inlined values can be used in situations where prepared statement expressions give sql problems, for example in some group-by clauses.
Note that using the same query with different inlined values effectively disables prepared statement caching for the query and may have a negative performance impact.
In case of a string will the value be validated, values that contain a single quote will not be inlined.
Returns:Object an inlined value appearing literally in the resulting SQL.
Sample
var query = datasources.db.example_data.order_details.createSelect();
var mult = query.columns.unitprice.multiply(query.inline(100, query.columns.unitprice));
query.result.add(mult);
query.result.add(query.columns.discount.max);
query.groupBy.add(mult);
inline(number, columnForType)
Create an inlined value converted to the type of the column.
QBColumncolumnForType convert value to type of the column
Returns:Object an inlined value converted to the type of the specified column.
Sample
var query = datasources.db.example_data.order_details.createSelect();
var mult = query.columns.unitprice.multiply(query.inline(100, query.columns.unitprice));
query.result.add(mult);
query.result.add(query.columns.discount.max);
query.groupBy.add(mult);
var query = datasources.db.example_data.order_details.createSelect();
var mult = query.columns.unitprice.multiply(query.inline(100, query.columns.unitprice));
query.result.add(mult);
query.result.add(query.columns.discount.max);
query.groupBy.add(mult);
