Servoy 2020 Documentation
Child pages
  • QBSelect

Versions Compared

Old Version 4

changes.mady.by.user Servoy Docs

Saved on

compared with

New Version 5

changes.mady.by.user Servoy Docs

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Rev: 1377846354666
Wiki Markup
{div:style=display:none}
DO NOT EDIT THE CONTENT OF THIS PAGE DIRECTLY (EXCEPT INSIDE THE DIV BELOW WITH ID=DESCRIPTION), UNLESS YOU KNOW WHAT YOU'RE DOING.
		THE STRUCTURE OF THE CONTENT IS VITAL IN BEING ABLE TO AUTO UPDATE THE CONTENT THROUGH THE DOC GENERATOR.\\		\\		Enter additional information related to this 'class' inside the \{div} macro with 'id=description'{div}
{div:id=description}{div}\\ 

{table:id=|class=servoy sSummary}{colgroup}{column:width=80px|padding=0px}{column}{column}{column}{colgroup}{tr:style=height: 30px;}{th:colspan=2}Property Summary{th}{tr}{tbody}{tr}{td}[QBLogicalCondition]{td}{td}[#and]
Create an AND\-condition to add conditions to.{td}{tr}{tbody}{tbody}{tr}{td}[QBColumns]{td}{td}[#columns]
Get columns from query{td}{tr}{tbody}{tbody}{tr}{td}[QBFunctions]{td}{td}[#functions]
Get the functions clause from a query, used for functions that are not tied to a column.{td}{tr}{tbody}{tbody}{tr}{td}[QBGroupBy]{td}{td}[#groupBy]
Get the group by clause from a query{td}{tr}{tbody}{tbody}{tr}{td}[QBLogicalCondition]{td}{td}[#having]
Get the having\-part of the query, used to add conditions.{td}{tr}{tbody}{tbody}{tr}{td}[QBJoins]{td}{td}[#joins]
Get the joins clause of this table based clause.{td}{tr}{tbody}{tbody}{tr}{td}[QBLogicalCondition]{td}{td}[#or]
Create an OR\-condition to add conditions to.{td}{tr}{tbody}{tbody}{tr}{td}[QBParameters]{td}{td}[#params]
Get the named parameters from a query.{td}{tr}{tbody}{tbody}{tr}{td}[QBTableClause]{td}{td}[#parent]
Get query builder parent table clause, this may be a query or a join clause.{td}{tr}{tbody}{tbody}{tr}{td}[QBResult]{td}{td}[#result]
Get the result part of the query, used to add result columns or values.{td}{tr}{tbody}{tbody}{tr}{td}[QBSelect]{td}{td}[#root]
Get query builder parent.{td}{tr}{tbody}{tbody}{tr}{td}[QBSorts]{td}{td}[#sort]
Get the sorting part of the query.{td}{tr}{tbody}{tbody}{tr}{td}[QBWhereCondition]{td}{td}[#where]
Get the where\-part of the query, used to add conditions.{td}{tr}{tbody}{table}\\ 

{table:id=|class=servoy sSummary}{colgroup}{column:width=80px|padding=0px}{column}{column}{column}{colgroup}{tr:style=height: 30px;}{th:colspan=2}Method Summary{th}{tr}{tbody}{tr}{td}[QBSelect]{td}{td}[#clearHaving]\()
Clear the having\-part of the query.{td}{tr}{tbody}{tbody}{tr}{td}[QBCondition]{td}{td}[#exists]\()
Get an exists\-condition from a subquery{td}{tr}{tbody}{tbody}{tr}{td}[QBColumn]{td}{td}[#getColumn]\(name)
Get a column from the table.{td}{tr}{tbody}{tbody}{tr}{td}[QBColumn]{td}{td}[#getColumn]\(columnTableAlias, name)
Get a column from the table with given alias.{td}{tr}{tbody}{tbody}{tr}{td}[QBParameter]{td}{td}[#getParameter]\()
Get or create a parameter for the query, this used to parameterize queries.{td}{tr}{tbody}{tbody}{tr}{td}[QBCondition]{td}{td}[#not]\(cond)
Create an negated condition.{td}{tr}{tbody}{tbody}{tr}{td}[QBCondition]{td}{td}[#not]\(cond)
Create an negated condition.{td}{tr}{tbody}{table}\\ 

{table:id=property|class=servoy sDetail}{colgroup}{column:width=100%|padding=0px}{column}{colgroup}{tr:style=height: 30px;}{th:colspan=1}Property Details{th}{tr}{tbody:id=and}{tr:id=name}{td}h6.and{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBLogicalCondition]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
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()))
	     )
	);
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=columns}{tr:id=name}{td}h6.columns{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBColumns]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
foundset.getQuery().columns
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=functions}{tr:id=name}{td}h6.functions{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBFunctions]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
/** @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)
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=groupBy}{tr:id=name}{td}h6.groupBy{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBGroupBy]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
/** @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)
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=having}{tr:id=name}{td}h6.having{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBLogicalCondition]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
/** @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)
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=joins}{tr:id=name}{td}h6.joins{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBJoins]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
foundset.getQuery().joins
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=or}{tr:id=name}{td}h6.or{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBLogicalCondition]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
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()))
	     )
	);
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=params}{tr:id=name}{td}h6.params{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBParameters]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
/** @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)
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=parent}{tr:id=name}{td}h6.parent{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBTableClause]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
/** @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)
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=result}{tr:id=name}{td}h6.result{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBResult]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
query.result.add(query.columns.company_id).add(query.columns.customerid)
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=root}{tr:id=name}{td}h6.root{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBSelect]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
/** @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)
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=sort}{tr:id=name}{td}h6.sort{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBSorts]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
/** @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)
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=where}{tr:id=name}{td}h6.where{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBWhereCondition]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
var query = foundset.getQuery()
query.where.add(query.columns.flag.eq(1))
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{table}\\ 

{table:id=function|class=servoy sDetail}{colgroup}{column:width=100%|padding=0px}{column}{colgroup}{tr:style=height: 30px;}{th:colspan=1}Method Details{th}{tr}{tbody:id=clearHaving}{tr:id=name}{td}h6.clearHaving{td}{tr}{tr:id=sig}{td}{span:style=margin-right: 5px;}[QBSelect]{span}{span:style=font-weight: bold;}clearHaving{span}{span}\(){span}{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBSelect]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
var q = foundset.getQuery()
q.where.add(q.columns.x.eq(100))
query.groupBy.clear.root.clearHaving()
foundset.loadRecords(q);
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=exists}{tr:id=name}{td}h6.exists{td}{tr}{tr:id=sig}{td}{span:style=margin-right: 5px;}[QBCondition]{span}{span:style=font-weight: bold;}exists{span}{span}\(){span}{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBCondition]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
foundset.query.where.add(query.exists(query2))
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=getColumn-String}{tr:id=name}{td}h6.getColumn{td}{tr}{tr:id=sig}{td}{span:style=margin-right: 5px;}[QBColumn]{span}{span:style=font-weight: bold;}getColumn{span}{span}\(name){span}{td}{tr}{tr:id=prs}{td}*Parameters*\\{div:class=sIndent}\{[String]} name -- the name of column to get
{div}{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBColumn]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
foundset.getQuery().getColumn('orderid')
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=getColumn-String_String}{tr:id=name}{td}h6.getColumn{td}{tr}{tr:id=sig}{td}{span:style=margin-right: 5px;}[QBColumn]{span}{span:style=font-weight: bold;}getColumn{span}{span}\(columnTableAlias, name){span}{td}{tr}{tr:id=prs}{td}*Parameters*\\{div:class=sIndent}\{[String]} columnTableAlias -- the alias for the table
\{[String]} name -- the name of column to get
{div}{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBColumn]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
foundset.getQuery().getColumn('orderid', 'opk')
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=getParameter}{tr:id=name}{td}h6.getParameter{td}{tr}{tr:id=sig}{td}{span:style=margin-right: 5px;}[QBParameter]{span}{span:style=font-weight: bold;}getParameter{span}{span}\(){span}{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBParameter]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
/** @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)
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=not-QBCondition}{tr:id=name}{td}h6.not{td}{tr}{tr:id=sig}{td}{span:style=margin-right: 5px;}[QBCondition]{span}{span:style=font-weight: bold;}not{span}{span}\(cond){span}{td}{tr}{tr:id=prs}{td}*Parameters*\\{div:class=sIndent}\{[QBCondition]} cond -- the condition to negate
{div}{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBCondition]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
foundset.query.where.add(query.not(query.columns.flag.eq(1)))
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=not-QBLogicalCondition}{tr:id=name}{td}h6.not{td}{tr}{tr:id=sig}{td}{span:style=margin-right: 5px;}[QBCondition]{span}{span:style=font-weight: bold;}not{span}{span}\(cond){span}{td}{tr}{tr:id=prs}{td}*Parameters*\\{div:class=sIndent}\{[QBLogicalCondition]} cond -- the logical condition to negate
{div}{td}{tr}{tr:id=ret}{td}*Returns*\\{div:class=sIndent}[QBCondition]{div}{td}{tr}{tr:id=sam}{td}*Sample*\\{div:class=sIndent}{code:language=javascript}
foundset.query.where.add(query.not(query.columns.flag.eq(1)))
{code}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{table}