Servoy 6.1.x
Child pages
  • QBSelect
Skip to end of metadata
Go to start of metadata


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
<pre>
// SELECT val2, COUNT(val2) FROM tab GROUP BY val2 ORDER BY COUNT(val2) DESC
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.
QBLogicalCondition #where
Get the where-part of the query, used to add conditions.

Method Summary
QBCondition #exists()
Get an exists-condition from a subquery
<pre>
// where exists (select 1 from tab where flag = ?)
query.
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)

Property Details
and
Create an AND-condition to add conditions to.
<pre>
// where (flag = ? and order_date is null) or (flag = ? and order_date > ?)
query.where().add(
query.or()
.add(
query.and()
.add(query.getColumn("flag").eq(new Integer(1)))
.add(query.getColumn("order_date").isNull())
)
.add(
query.and()
.add(query.getColumn("flag").eq(new Integer(2)))
.add(query.getColumn("order_date").gt(new Date()))
)
);
</pre>
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.
<pre>
// select pk from tab where floor(val / ?) > pk [1999]
IQueryBuilder query = queryFactory.createSelect(table.getDataSource()).result().addPk().getParent();
query.where()
.add(query.functions().floor(query.getColumn(val.getName()).divide(new Integer(1999))).gt(query.getColumn(id.getName())))
.getRoot().sort().add(query.getColumn(id.getName()).asc());
</pre>
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
<pre>
// SELECT val2, COUNT(val2) FROM tab GROUP BY val2 ORDER BY COUNT(val2) DESC
query.result().add(query.getColumn(val2.getName())).add(query.getColumn(val2.getName()).count())
.getParent().groupBy().add(val2.getName())
.getParent().sort().add(query.getColumn(val2.getName()).count().desc());
</pre>
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.
<pre>
// select value from tab group by value having count(value) > 1
query.result().add(query.getColumn("value"))
.getParent().groupBy().add("value")
.getParent().having().add("value").count().gt(Integer.valueOf(1)));
</pre>
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.
<p>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.
<pre>
// where custid = ? and (order_date is null or order_date > ?)
query.where().add(query.getColumn("custid").eq(new Integer(200)))
.add(
query.or()
.add(query.getColumn("order_date").isNull())
.add(query.getColumn("order_date").gt(new Date()))
);
</pre>
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.
<pre>
query.result().add(query.getColumn("id")).add.query.getColumn("note"); // select id, note from tab
</pre>
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.
<pre>
query.sort().add(query.getColumn("note").desc()).add(query.getColumn("id")); // order by note desc, id asc
</pre>
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.
<pre>
query.where().add(query.getColumn("flag").eq(new Integer(1)).add(query.getColumn("foo").isNull(); // where flag = 1 and foo is null
</pre>
Returns
QBLogicalCondition
Sample
var query = foundset.getQuery()
query.where.add(query.columns.flag.eq(1))

Method Details
exists

QBCondition exists ()

Get an exists-condition from a subquery
<pre>
// where exists (select 1 from tab where flag = ?)
query.where().add(query.exists(subQuery.result().addValue(new Integer(1)).getParent().where().add(subQuery.getColumn("flag").eq("T")).getRoot()));

// or simple variant: adds 'select 1' and calls getRoot()
query.where().add(query.exists(subQuery.where().add(subQuery.getColumn("flag").eq("T"))));
</pre>
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.
<p>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.
<pre>
query.where().add(query.getColumn("flag").eq(query.getParameter("myvar"));
query.getParameter("myvar").setvalue(new Integer(1));
</pre>
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.
<pre>
// where not (order_date is null or order_date > ?)
query.where().add(query.not(
query.or()
.add(query.getColumn("order_date").isNull())
.add(query.getColumn("order_date").gt(new Date()))
)
);
</pre>
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)

Replace with description
Parameters
{QBLogicalCondition} cond – the logical condition to negate
Returns
QBCondition
Sample
foundset.query.where.add(query.not(query.columns.flag.eq(1)))
  • No labels