Servoy 7.x.x Documentation
Child pages
  • QBGroupBy

Versions Compared

Old Version 2

changes.mady.by.user Servoy Docs

Saved on

compared with

New Version 3

changes.mady.by.user Servoy Docs

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Wiki Markup
{hidden}
DO NOT EDIT THE CONTENT OF THIS PAGE DIRECTLY, UNLESS YOU KNOW WHAT YOU'RE DOING.
		THE STRUCTURE OF THE CONTENT IS VITAL IN BEING ABLE TO EXTRACT CHANGES FROM THE PAGE AND MERGE THEM BACK INTO SERVOY SOURCE{hidden}
{sub-section:description|text=}{sub-section}\\ 

{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}[QBSelect]{td}{td}[#parent]
Get query builder parent table clause, this may be a query or a join clause.{td}{tr}{tbody}{tbody}{tr}{td}[QBSelect]{td}{td}[#root]
Get query builder parent.{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}[QBGroupBy]{td}{td}[#add]\(column)
Add column name to group\-by clause.{td}{tr}{tbody}{tbody}{tr}{td}[QBGroupBy]{td}{td}[#add]\(function)
Add column name to group\-by clause.{td}{tr}{tbody}{tbody}{tr}{td}[QBGroupBy]{td}{td}[#addPk]\()
Add the tables' primary pk columns in alphabetical order to the group by clause.{td}{tr}{tbody}{tbody}{tr}{td}[QBGroupBy]{td}{td}[#clear]\()
Clear the to group\-by clause.{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=parent|class=node}{tr:id=name}{td}h6.parent{td}{tr}{builder-show:permission=edit}{tr:id=des}{td}{sub-section:parent_des|trigger=button|text=}{sub-section}{sub-section:parent_des|trigger=none|class=sIndent}GetReplace query builder parent table clause, this may be a query or a join clause.with description{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:parent_snc|trigger=button|text=}{sub-section}{sub-section:parent_snc|trigger=none|class=sIndent} Replace with version info{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=prs}{td}*Parameters*\\{sub-section:parent_prs|trigger=button|text=}{sub-section}{sub-section:parent_prs|trigger=none|class=sIndent}{sub-section}{td}{tr}{builder-show}{tr:id=ret}{td}*Returns*\\{sub-section:parent_ret|trigger=button|text=}{sub-section}{sub-section:parent_ret|trigger=none|class=sIndent}[QBSelect]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:parent_see|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:parent_see|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:parent_link|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:parent_link|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:parent_sam|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:parent_sam|trigger=none}{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}{sub-section}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=root|class=node}{tr:id=name}{td}h6.root{td}{tr}{builder-show:permission=edit}{tr:id=des}{td}{sub-section:root_des|trigger=button|text=}{sub-section}{sub-section:root_des|trigger=none|class=sIndent}GetReplace query builder parent.with description{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:root_snc|trigger=button|text=}{sub-section}{sub-section:root_snc|trigger=none|class=sIndent} Replace with version info{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=prs}{td}*Parameters*\\{sub-section:root_prs|trigger=button|text=}{sub-section}{sub-section:root_prs|trigger=none|class=sIndent}{sub-section}{td}{tr}{builder-show}{tr:id=ret}{td}*Returns*\\{sub-section:root_ret|trigger=button|text=}{sub-section}{sub-section:root_ret|trigger=none|class=sIndent}[QBSelect]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:root_see|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:root_see|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:root_link|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:root_link|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:root_sam|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:root_sam|trigger=none}{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}{sub-section}{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=add-QBColumn|class=node}{tr:id=name}{td}h6.add{td}{tr}{tr:id=sig}{td}{span:style=float: left; margin-right: 5px;}[QBGroupBy]{span}{span:id=iets|style=float: left; font-weight: bold;}add{span}{span:id=iets|style=float: left;}\(column){span}{td}{tr}{tr:builder-show:permission=edit}{tr:id=des}{td}{sub-section:add-QBColumn_des|trigger=button|text=}{sub-section}{sub-section:add-QBColumn_des|trigger=none|class=sIndent}AddReplace column name to group\-by clause.

Same as query.groupBy().add(join.getColumn("value"))with description{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:add-QBColumn_snc|trigger=button|text=}{sub-section}{sub-section:add-QBColumn_snc|trigger=none|class=sIndent} Replace with version info{sub-section}{td}{tr}{builder-show}{tr:id=prs}{td}*Parameters*\\{sub-section:add-QBColumn_prs|trigger=button|text=}{sub-section}{sub-section:add-QBColumn_prs|trigger=none|class=sIndent}\{[QBColumn]} column -- the column to add to the query condition
{sub-section}{td}{tr}{tr:id=ret}{td}*Returns*\\{sub-section:add-QBColumn_ret|trigger=button|text=}{sub-section}{sub-section:add-QBColumn_ret|trigger=none|class=sIndent}[QBGroupBy]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:add-QBColumn_see|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:add-QBColumn_see|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:add-QBColumn_link|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:add-QBColumn_link|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:add-QBColumn_sam|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:add-QBColumn_sam|trigger=none}{code:language=javascript}
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders')
query.groupBy.add(query.columns.orderid) // 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}{sub-section}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=add-QBFunction|class=node}{tr:id=name}{td}h6.add{td}{tr}{tr:id=sig}{td}{span:style=float: left; margin-right: 5px;}[QBGroupBy]{span}{span:id=iets|style=float: left; font-weight: bold;}add{span}{span:id=iets|style=float: left;}\(function){span}{td}{tr}{builder-show:permission=edit}{tr:id=des}{td}{sub-section:add-QBFunction_des|trigger=button|text=}{sub-section}{sub-section:add-QBFunction_des|trigger=none|class=sIndent}AddReplace column name to group\-by clause.

Same as query.groupBy().add(join.getColumn("value"))with description{sub-section}{td}{tr}{trbuilder-show}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:add-QBFunction_snc|trigger=button|text=}{sub-section}{sub-section:add-QBFunction_snc|trigger=none|class=sIndent} Replace with version info{sub-section}{td}{tr}{builder-show}{tr:id=prs}{td}*Parameters*\\{sub-section:add-QBFunction_prs|trigger=button|text=}{sub-section}{sub-section:add-QBFunction_prs|trigger=none|class=sIndent}\{[QBFunction]} function -- the function to add to the query
{sub-section}{td}{tr}{tr:id=ret}{td}*Returns*\\{sub-section:add-QBFunction_ret|trigger=button|text=}{sub-section}{sub-section:add-QBFunction_ret|trigger=none|class=sIndent}[QBGroupBy]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:add-QBFunction_see|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:add-QBFunction_see|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:add-QBFunction_link|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:add-QBFunction_link|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:add-QBFunction_sam|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:add-QBFunction_sam|trigger=none}{code:language=javascript}
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders')
query.groupBy.add(query.columns.orderid) // 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}{sub-section}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=addPk|class=node}{tr:id=name}{td}h6.addPk{td}{tr}{tr:id=sig}{td}{span:style=float: left; margin-right: 5px;}[QBGroupBy]{span}{span:id=iets|style=float: left; font-weight: bold;}addPk{span}{span:id=iets|style=float: left;}\(){span}{td}{tr}{builder-show:permission=edit}{tr:id=des}{td}{sub-section:addPk_des|trigger=button|text=}{sub-section}{sub-section:addPk_des|trigger=none|class=sIndent}AddReplace the tables' primary pk columns in alphabetical order to the group by clause.with description{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:addPk_snc|trigger=button|text=}{sub-section}{sub-section:addPk_snc|trigger=none|class=sIndent} Replace with version info{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=prs}{td}*Parameters*\\{sub-section:addPk_prs|trigger=button|text=}{sub-section}{sub-section:addPk_prs|trigger=none|class=sIndent}{sub-section}{td}{tr}{builder-show}{tr:id=ret}{td}*Returns*\\{sub-section:addPk_ret|trigger=button|text=}{sub-section}{sub-section:addPk_ret|trigger=none|class=sIndent}[QBGroupBy]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:addPk_see|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:addPk_see|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:addPk_link|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:addPk_link|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:addPk_sam|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:addPk_sam|trigger=none}{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}{sub-section}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=clear|class=node}{tr:id=name}{td}h6.clear{td}{tr}{tr:id=sig}{td}{span:style=float: left; margin-right: 5px;}[QBGroupBy]{span}{span:id=iets|style=float: left; font-weight: bold;}clear{span}{span:id=iets|style=float: left;}\(){span}{td}{tr}{builder-show:permission=edit}{tr:id=des}{td}{sub-section:clear_des|trigger=button|text=}{sub-section}{sub-section:clear_des|trigger=none|class=sIndent}ClearReplace the to group\-by clause.with description{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:clear_snc|trigger=button|text=}{sub-section}{sub-section:clear_snc|trigger=none|class=sIndent} Replace with version info{sub-section}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=prs}{td}*Parameters*\\{sub-section:clear_prs|trigger=button|text=}{sub-section}{sub-section:clear_prs|trigger=none|class=sIndent}{sub-section}{td}{tr}{builder-show}{tr:id=ret}{td}*Returns*\\{sub-section:clear_ret|trigger=button|text=}{sub-section}{sub-section:clear_ret|trigger=none|class=sIndent}[QBGroupBy]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:clear_see|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:clear_see|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:clear_link|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:clear_link|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:clear_sam|trigger=button|text=}{sub-section}{div:class=sIndent}{sub-section:clear_sam|trigger=none}{code:language=javascript}
var q = foundset.getQuery()
q.where.add(q.columns.x.eq(100))
query.groupBy.clear.root.clearHaving()
foundset.loadRecords(q);
{code}{sub-section}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{table}