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

Type

Name

Summary

Get the aggregates clause from a query, used for aggregates that are not tied to a column.

Create an AND-condition to add conditions to.

Create an case searched expression.

Get all the columns of the datasource that can be used for this query (select or where clause)

Specifies a comment of the query.

Get the functions clause from a query, used for functions that are not tied to a column.

Get the group by clause from a query

Get the having-part of the query, used to add conditions.

Get the joins clause of this table based clause.

Create an OR-condition to add conditions to.

Get the named parameters from a query.

Get query builder parent table clause, this may be a query or a join clause.

Get the result part of the query, used to add result columns or values.

Get query builder parent.

Get the sorting part of the query.

Get the where-part of the query, used to add conditions.

Methods Summarized

Type

Name

Summary

Clear the having-part of the query.

Get an exists-condition from a subquery

Get a column from the table.

Get a column from the table with given alias.

Performs a sql query with a query builder object.

Returns the datasource for this.

Returns a foundset object for a specified pk base query.

Get or create a parameter for the query, this used to parameterize queries.

Returns the internal SQL of the QBSelect.

Returns the parameters for the internal SQL of the QBSelect.

Returns the table alias for this.

Create an inlined value.

Create an inlined value converted to the type of the column.

Create an inlined (quoted) value.

Create an negated condition.

Properties Detailed

aggregates

Get the aggregates clause from a query, used for aggregates that are not tied to a column.

Type QBAggregates

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

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

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

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

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

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

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().joins

or

Create an OR-condition to add conditions to.

Type QBLogicalCondition

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

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

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

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

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

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

Sample

foundset.query.where.add(query.exists(query2))