QBSelect
Overview
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.
Properties Summarized
Get the aggregates clause from a query, used for aggregates that are not tied to a column.
Get all the columns of the datasource that can be used for this query (select or where clause)
Get the functions clause from a query, used for functions that are not tied to a column.
Methods Summarized
Performs a sql query with a query builder object.
Get or create a parameter for the query, this used to parameterize queries.
Properties Detailed
aggregates
Get the aggregates clause from a query, used for aggregates that are not tied to a column.
Type QBAggregates the aggregates clause of the query for non-column-bound aggregates.
Sample
var query = datasources.db.example_data.orders.createSelect();
query.result.add(query.aggregates.count().add(query.columns.countryCode)
query.groupBy.add(query.columns.countryCode)
var ds = databaseManager.getDataSetByQuery(query, 100);and
Create an AND-condition to add conditions to.
Type QBLogicalCondition an AND-condition for adding logical conditions.
Sample
query.where.add(
query.or
.add(
query.and
.add(query.columns.flag.eq(1))
.add(query.columns.order_date.isNull)
)
.add(
query.and
.add(query.columns.flag.eq(2))
.add(query.columns.order_date.gt(new Date()))
)
);case
Create an case searched expression.
Type QBCase 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)
Type QBColumns
Sample
var query = foundset.getQuery();
query.result.add(query.columns.name, "name");
query.where.add(query.columns.orderdate.isNull)comment
Specifies a comment of the query.
Type String the comment associated with this query.
Sample
var query = datasources.db.example_data.orders.createSelect();
query.comment = 'Query comment'functions
Get the functions clause from a query, used for functions that are not tied to a column.
Type QBFunctions the functions clause of the query for non-column-bound functions.
Sample
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.upper.eq(query.functions.upper('servoy')))
foundset.loadRecords(query)groupBy
Get the group by clause from a query
Type QBGroupBy the group-by clause of the query.
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)having
Get the having-part of the query, used to add conditions. The conditions added here are AND-ed.
Type QBLogicalCondition 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.
Type QBJoins
Sample
foundset.getQuery().joinsor
Create an OR-condition to add conditions to.
Type QBLogicalCondition an OR-condition for adding logical conditions.
Sample
query.where.add(
query.or
.add(
query.and
.add(query.columns.flag.eq(1))
.add(query.columns.order_date.isNull)
)
.add(
query.and
.add(query.columns.flag.eq(2))
.add(query.columns.order_date.gt(new Date()))
)
);params
Get the named parameters from a query.
Type QBParameters the named parameters of the query.
Sample
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.contact_id.eq(query.getParameter('mycontactid')))
// load orders where contact_id = 100
query.params['mycontactid'] = 100
foundset.loadRecords(query)
// load orders where contact_id = 200
query.params['mycontactid'] = 200
foundset.loadRecords(query)parent
Get query builder parent table clause, this may be a query or a join clause.
Type QBTableClause
Sample
var query = datasources.db.example_data.person.createSelect();
query.where.add(query.joins.person_to_parent.joins.person_to_parent.columns.name.eq('john'))
foundset.loadRecords(query)result
Get the result part of the query, used to add result columns or values.
Type QBResult the result part of the query for adding result columns or values.
Sample
query.result.add(query.columns.company_id).add(query.columns.customerid)root
Get query builder parent.
Type QBSelect
Sample
var subquery = datasources.db.example_data.order_details.createSelect();
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query
.or
.add(query.columns.order_id.not.isin([1, 2, 3]))
.add(query.exists(
subquery.where.add(subquery.columns.orderid.eq(query.columns.order_id)).root
))
)
foundset.loadRecords(query)sort
Get the sorting part of the query.
Type QBSorts the sorting part of the query.
Sample
var query = datasources.db.example_data.orders.createSelect();
query.sort
.add(query.joins.orders_to_order_details.columns.quantity.desc)
.add(query.columns.companyid)
foundset.loadRecords(query)where
Get the where-part of the query, used to add conditions. The conditions added here are AND-ed.
Type QBLogicalCondition the where-part of the query for adding conditions.
Sample
var query = foundset.getQuery()
query.where.add(query.columns.flag.eq(1))Methods Detailed
clearHaving()
Clear the having-part of the query.
Returns: QBSelect the updated query builder after clearing the having clause.
Sample
var q = foundset.getQuery()
q.where.add(q.columns.x.eq(100))
query.groupBy.clear.root.clearHaving()
foundset.loadRecords(q);exists(query)
Get an exists-condition from a subquery
Parameters
Object query the sub query
Returns: QBCondition a condition that checks the existence of the given subquery.
Sample
foundset.query.where.add(query.exists(query2))getColumn(name)
Get a column from the table.
Parameters
String name the name of column to get
Returns: QBColumn the QBColumn representing the specified column name.
Sample
foundset.getQuery().getColumn('orderid')getColumn(columnTableAlias, name)
Get a column from the table with given alias. The alias may be of the main table or any level deep joined table.
Parameters
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
Number max_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
Number max_returned_rows The maximum number of rows returned by the query.
Boolean useTableFilters 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)getDataSource()
Returns the datasource for this.
Returns: String the dataSource
getFoundSet()
Returns a foundset object for a specified pk base query. Same as databaseManager.getFoundSet(QBSelect).
Returns: JSFoundSet A new JSFoundset with the query as its base query.
Sample
var qb = datasources.db.example_data.orders.createSelect();
qb.result.addPk();
qb.where.add(qb.columns.product_id.eq(1))
var fs = qb.getFoundSet();getParameter(name)
Get or create a parameter for the query, this used to parameterize queries.
Parameters
String name the name of the parameter
Returns: QBParameter the parameter with the specified name.
Sample
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.contact_id.eq(query.getParameter('mycontactid')))
// load orders where contact_id = 100
query.params['mycontactid'] = 100
foundset.loadRecords(query)
// load orders where contact_id = 200
query.params['mycontactid'] = 200
foundset.loadRecords(query)getSQL()
Returns the internal SQL of the QBSelect. Table filters are on by default.
Returns: String String representing the sql of the Query Builder.
Sample
var sql = query.getSQL(true)getSQL(includeFilters)
Returns the internal SQL of the QBSelect. Table filters are on by default.
Parameters
Boolean includeFilters include the table filters [default true].
Returns: String String representing the sql of the Query Builder.
Sample
var sql = query.getSQL(true)getSQLParameters()
Returns the parameters for the internal SQL of the QBSelect. Table filters are on by default.
Returns: Array An Array with the sql parameter values.
Sample
var parameters = query.getSQLParameters(true)getSQLParameters(includeFilters)
Returns the parameters for the internal SQL of the QBSelect. Table filters are on by default.
Parameters
Boolean includeFilters include the table filters [default true].
Returns: Array An Array with the sql parameter values.
Sample
var parameters = query.getSQLParameters(true)getTableAlias()
Returns the table alias for this.
Returns: String the tableAlias
inline(number)
Create an inlined value. An inlined value is a value that will appear literally in the resulting sql. For example
query.where.add(query.columns.custid.eq(query.inline(200)))
results in sql
where custid = 200
And
query.where.add(query.columns.custid.eq(200))
results in sql
where custid = ?
with prepared statement value 200.
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.
Parameters
Number number value to inline
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.
Parameters
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);inline(string)
Create an inlined (quoted) value.
Parameters
String string value to inline
Returns: Object an inlined (quoted) string value.
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);not(cond)
Create an negated condition.
Parameters
QBCondition cond the condition to negate
Returns: QBCondition a negated version of the specified condition.
Sample
foundset.query.where.add(query.not(query.columns.flag.eq(1)))not(cond)
Create an negated condition.
Parameters
QBLogicalCondition cond the logical condition to negate
Returns: QBCondition a negated version of the specified logical condition.
Sample
foundset.query.where.add(query.not(query.columns.flag.eq(1)))Last updated
Was this helpful?