Servoy 2020 Documentation
Child pages
  • QBSelect
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 10 Next »


Property Summary
QBLogicalCondition and
Create an AND-condition to add conditions to.
QBColumns columns
Get columns from query
QBFunctions functions
Get the functions clause from a query, used for functions that are not tied to a column.
QBGroupBy groupBy
Get the group by clause from a query
QBLogicalCondition having
Get the having-part of the query, used to add conditions.
QBJoins joins
Get the joins clause of this table based clause.
QBLogicalCondition or
Create an OR-condition to add conditions to.
QBParameters params
Get the named parameters from a query.
QBTableClause parent
Get query builder parent table clause, this may be a query or a join clause.
QBResult result
Get the result part of the query, used to add result columns or values.
QBSelect root
Get query builder parent.
QBSorts sort
Get the sorting part of the query.
QBWhereCondition where
Get the where-part of the query, used to add conditions.

Method Summary
QBSelect clearHaving()
Clear the having-part of the query.
QBCondition exists()
Get an exists-condition from a subquery
QBColumn getColumn(name)
Get a column from the table.
QBColumn getColumn(columnTableAlias, name)
Get a column from the table with given alias.
QBParameter getParameter()
Get or create a parameter for the query, this used to parameterize queries.
QBCondition not(cond)
Create an negated condition.
QBCondition not(cond)
Create an negated condition.

Property Details
and
Create an AND-condition to add conditions to.
Returns
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.column.order_date.gt(new Date()))
	     )
	);
columns
Get columns from query
Returns
QBColumns
Sample
foundset.getQuery().columns
functions
Get the functions clause from a query, used for functions that are not tied to a column.
Returns
QBFunctions
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$
query.where.add(query.columns.shipname.upper.eq(query.functions.upper('servoy'))) //$NON-NLS-1$
foundset.loadRecords(query)
groupBy
Get the group by clause from a query
Returns
QBGroupBy
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders')
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.
Returns
QBLogicalCondition
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders')
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
QBJoins
Sample
foundset.getQuery().joins
or
Create an OR-condition to add conditions to.
Returns
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.column.order_date.gt(new Date()))
	     )
	);
params
Get the named parameters from a query.
Returns
QBParameters
Sample
/** @type {QBSelect<db:/example_data/orders>} */
	var query = databaseManager.createSelect('db:/example_data/orders')
	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.
Returns
QBTableClause
Sample
/** @type {QBSelect<db:/example_data/person>} */
	var query = databaseManager.createSelect('db:/example_data/person')
	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.
Returns
QBResult
Sample
query.result.add(query.columns.company_id).add(query.columns.customerid)
root
Get query builder parent.
Returns
QBSelect
Sample
/** @type {QBSelect<db:/example_data/order_details>} */
	var subquery = databaseManager.createSelect('db:/example_data/order_details')

 /** @type {QBSelect<db:/example_data/orders>} */
	var query = databaseManager.createSelect('db:/example_data/orders')
	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.
Returns
QBSorts
Sample
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders')
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.
Returns
QBWhereCondition
Sample
var query = foundset.getQuery()
query.where.add(query.columns.flag.eq(1))

Method Details
clearHaving
QBSelect 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
QBCondition exists ()
Get an exists-condition from a subquery
Returns
QBCondition
Sample
foundset.query.where.add(query.exists(query2))
getColumn
QBColumn getColumn (name)
Get a column from the table.
Parameters
{String} name - the name of column to get
Returns
QBColumn
Sample
foundset.getQuery().getColumn('orderid')
getColumn
QBColumn 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
{String} columnTableAlias - the alias for the table
{String} name - the name of column to get
Returns
QBColumn
Sample
foundset.getQuery().getColumn('orderid', 'opk')
getParameter
QBParameter getParameter ()
Get or create a parameter for the query, this used to parameterize queries.
Returns
QBParameter
Sample
/** @type {QBSelect<db:/example_data/orders>} */
	var query = databaseManager.createSelect('db:/example_data/orders')
	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)
not
QBCondition not (cond)
Create an negated condition.
Parameters
{QBCondition} cond - the condition to negate
Returns
QBCondition
Sample
foundset.query.where.add(query.not(query.columns.flag.eq(1)))
not
QBCondition not (cond)
Create an negated condition.
Parameters
{QBLogicalCondition} cond - the logical condition to negate
Returns
QBCondition
Sample
foundset.query.where.add(query.not(query.columns.flag.eq(1)))

  • No labels