Servoy 2020 Documentation
Child pages
  • QBJoins

Versions Compared

Old Version 1

changes.mady.by.user Servoy Docs

Saved on

compared with

New Version Current

changes.mady.by.user Servoy Docs

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
{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:padding=0px|width=80px}{column}{column}{column}{colgroup}{tr:style=height: 30px;}{th:colspan=2}Property Summary{th}{tr}{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}[QBSelect]{td}{td}[#root] Get query builder parent.{td}{tr}{tbody}{table}\\ {table:id=|class=servoy sSummary}{colgroup}{column:padding=0px|width=80px}{column}{column}{column}{colgroup}{tr:style=height: 30px;}{th:colspan=2}Method Summary{th}{tr}{tbody}{tr}{td}[QBJoin]{td}{td}[#add]\(dataSource) Add a join with join type IQueryBuilderJoin#LEFT\_OUTER\_JOIN and no alias for the joining table.{td}{tr}{tbody}{tbody}{tr}{td}[QBJoin]{td}{td}[#add]\(dataSource, joinType) Add a join with no alias for the joining table.{td}{tr}{tbody}{tbody}{tr}{td}[QBJoin]{td}{td}[#add]\(dataSource, joinType, alias) Add a join clause from the parent query builder part to the specified data source.{td}{tr}{tbody}{tbody}{tr}{td}[QBJoin]{td}{td}[#add]\(dataSourceOrRelation, alias) Add a join based on relation or add a manual join.{td}{tr}{tbody}{table}\\ {table:id=property|class=servoy sDetail}{colgroup}{column:padding=0px|width=100%}{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}{tr:id=des}{td}{sub-section:parent_des|text=|trigger=button}{sub-section}{sub-section:parent_des|trigger=none|class=sIndent}
Wiki Markup
Cache
indextrue
refresh100d
showRefreshtrue
iddoc
titleRefresh page
showDatetrue
retryEnable

servoy sReturnTypes2100%height: 30px;2Supported ClientssWordListSmartClientsWordListWebClientsWordListNGClient

servoy sSummary12%30%58%height: 30px;3Property SummaryGet query builder parent table clause, this may be a query or a join clause.Get query builder parent.

servoy sSummary12%30%58%height: 30px;3Methods SummaryAdd a join clause from the parent query builder part to a derived table based on another query.Add a join clause from the parent query builder part to a derived table based on another query.Add a join with join type IQueryBuilderJoin#LEFT_OUTER_JOIN and no alias for the joining table.Add a join with no alias for the joining table.Add a join clause from the parent query builder part to the specified data source.Add a join based on relation or add a manual join.Remove the joins that are not used anywhere in the query.

propertyservoy sDetail2100%height:30px2Property Detailsparentname

parent

dessIndent
Get query builder parent table clause, this may be a query or a join clause.
{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:parent_snc|text=|trigger=button}{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|text=|trigger=button}{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|text=|trigger=button}{sub-section}{sub-section:parent_ret|trigger=none|class=sIndent}[QBTableClause]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:parent_see|text=|trigger=button}{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|text=|trigger=button}{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|text=|trigger=button}{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}{tr:id=des}{td}{sub-section:root_des|text=|trigger=button}{sub-section}{sub-section:root_des|trigger=none|class=sIndent}Get query builder parent.{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:root_snc|text=|trigger=button}{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|text=|trigger=button}{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|text=|trigger=button}{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|text=|trigger=button}{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|text=|trigger=button}{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|text=|trigger=button}{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:padding=0px|width=100%}{column}{colgroup}{tr:style=height: 30px;}{th:colspan=1}Method Details{th}{tr}{tbody:id=add-String|class=node}{tr:id=name}{td}h6.add{td}{tr}{tr:id=sig}{td}{span:style=float: left; margin-right: 5px;}[QBJoin]{span}{span:id=iets|style=float: left; font-weight: bold;}add{span}{span:id=iets|style=float: left;}\(dataSource){span}{td}{tr}{tr:id=des}{td}{sub-section:add-String_des|text=|trigger=button}{sub-section}{sub-section:add-String_des|trigger=none|class=sIndent}
ret
Returns
sIndentclients
Supported Clients
sIndentSmartClient,WebClient,NGClientsam
Sample
sIdentjavascriptlastDetailRowrootname
root
dessIndent
Get query builder parent.
ret
Returns
sIndentclients
Supported Clients
sIndentSmartClient,WebClient,NGClientsam
Sample
sIdentjavascriptlastDetailRow
functionservoy sDetail2100%height:30px2Methods Detailsaddname
add(subqueryBuilder, joinType)
dessIndent
Add a join clause from the parent query builder part to a derived table based on another query.
prs
Parameters
sIndentsubqueryBuilder ;joinType ;ret
Returns
sIndentclients
Supported Clients
sIndentSmartClient,WebClient,NGClientsam
Sample
sIdentjavascriptlastDetailRowaddname
add(subqueryBuilder, joinType, alias)
dessIndent
Add a join clause from the parent query builder part to a derived table based on another query.
prs
Parameters
sIndentsubqueryBuilder ;joinType ;alias ;ret
Returns
sIndentclients
Supported Clients
sIndentSmartClient,WebClient,NGClientsam
Sample
sIdentjavascriptlastDetailRowaddname
add(dataSource)
dessIndent
Add a join with join type IQueryBuilderJoin#LEFT
\
_OUTER
\
_JOIN and no alias for the joining table.
{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:add-String_snc|text=|trigger=button}{sub-section}{sub-section:add-String_snc|trigger=none|class=sIndent} Replace with version info{sub-section}{td}{tr}{builder-show}{tr:id=prs}{td}*Parameters*\\{sub-section:add-String_prs|text=|trigger=button}{sub-section}{sub-section:add-String_prs|trigger=none|class=sIndent}\{[String]} dataSource -- data source
{sub-section}{td}{tr}{tr:id=ret}{td}*Returns*\\{sub-section:add-String_ret|text=|trigger=button}{sub-section}{sub-section:add-String_ret|trigger=none|class=sIndent}[QBJoin]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:add-String_see|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:add-String_see|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:add-String_link|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:add-String_link|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:add-String_sam|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:add-String_sam|trigger=none}{code:language=javascript}
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders')
/** @type {QBJoin<db:/example_data/order_details>} */
var join = query.joins.add('db:/example_data/order_details', JSRelation.INNER_JOIN, 'odetail')
join.on.add(join.columns.orderid.eq(query.columns.orderid))
query.where.add(join.columns.quantity.le(10))
foundset.loadRecords(query)
{code}{sub-section}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=add-String_Number|class=node}{tr:id=name}{td}h6.add{td}{tr}{tr:id=sig}{td}{span:style=float: left; margin-right: 5px;}[QBJoin]{span}{span:id=iets|style=float: left; font-weight: bold;}add{span}{span:id=iets|style=float: left;}\(dataSource, joinType){span}{td}{tr}{tr:id=des}{td}{sub-section:add-String_Number_des|text=|trigger=button}{sub-section}{sub-section:add-String_Number_des|trigger=none|class=sIndent}
prs
Parameters
sIndentdataSourcedata sourceret
Returns
sIndentclients
Supported Clients
sIndentSmartClient,WebClient,NGClientsam
Sample
sIdentjavascriptlastDetailRowaddname
add(dataSource, joinType)
dessIndent
Add a join with no alias for the joining table.
{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:add-String_Number_snc|text=|trigger=button}{sub-section}{sub-section:add-String_Number_snc|trigger=none|class=sIndent} Replace with version info{sub-section}{td}{tr}{builder-show}{tr:id=prs}{td}*Parameters*\\{sub-section:add-String_Number_prs|text=|trigger=button}{sub-section}{sub-section:add-String_Number_prs|trigger=none|class=sIndent}\{[String]} dataSource -- data source
\{[Number]} joinType -- join type, one of IQueryBuilderJoin#LEFT\_OUTER\_JOIN, IQueryBuilderJoin#INNER\_JOIN, IQueryBuilderJoin#RIGHT\_OUTER\_JOIN, IQueryBuilderJoin#FULL\_JOIN
{sub-section}{td}{tr}{tr:id=ret}{td}*Returns*\\{sub-section:add-String_Number_ret|text=|trigger=button}{sub-section}{sub-section:add-String_Number_ret|trigger=none|class=sIndent}[QBJoin]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:add-String_Number_see|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:add-String_Number_see|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:add-String_Number_link|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:add-String_Number_link|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:add-String_Number_sam|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:add-String_Number_sam|trigger=none}{code:language=javascript}
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders')
/** @type {QBJoin<db:/example_data/order_details>} */
var join = query.joins.add('db:/example_data/order_details', JSRelation.INNER_JOIN, 'odetail')
join.on.add(join.columns.orderid.eq(query.columns.orderid))
query.where.add(join.columns.quantity.le(10))
foundset.loadRecords(query)
{code}{sub-section}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=add-String_Number_String|class=node}{tr:id=name}{td}h6.add{td}{tr}{tr:id=sig}{td}{span:style=float: left; margin-right: 5px;}[QBJoin]{span}{span:id=iets|style=float: left; font-weight: bold;}add{span}{span:id=iets|style=float: left;}\(dataSource, joinType, alias){span}{td}{tr}{tr:id=des}{td}{sub-section:add-String_Number_String_des|text=|trigger=button}{sub-section}{sub-section:add-String_Number_String_des|trigger=none|class=sIndent}
prs
Parameters
sIndentdataSourcedata sourcejoinTypejoin type, one of QBJoin.LEFT_OUTER_JOIN, QBJoin.INNER_JOIN, QBJoin.RIGHT_OUTER_JOIN, QBJoin.FULL_JOINret
Returns
sIndentclients
Supported Clients
sIndentSmartClient,WebClient,NGClientsam
Sample
sIdentjavascriptlastDetailRowaddname
add(dataSource, joinType, alias)
dessIndent
Add a join clause from the parent query builder part to the specified data source.
{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:add-String_Number_String_snc|text=|trigger=button}{sub-section}{sub-section:add-String_Number_String_snc|trigger=none|class=sIndent} Replace with version info{sub-section}{td}{tr}{builder-show}{tr:id=prs}{td}*Parameters*\\{sub-section:add-String_Number_String_prs|text=|trigger=button}{sub-section}{sub-section:add-String_Number_String_prs|trigger=none|class=sIndent}\{[String]} dataSource -- data source
\{[Number]} joinType -- join type, one of IQueryBuilderJoin#LEFT\_OUTER\_JOIN, IQueryBuilderJoin#INNER\_JOIN, IQueryBuilderJoin#RIGHT\_OUTER\_JOIN, IQueryBuilderJoin#FULL\_JOIN
\{[String]} alias -- the alias for joining table
{sub-section}{td}{tr}{tr:id=ret}{td}*Returns*\\{sub-section:add-String_Number_String_ret|text=|trigger=button}{sub-section}{sub-section:add-String_Number_String_ret|trigger=none|class=sIndent}[QBJoin]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:add-String_Number_String_see|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:add-String_Number_String_see|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:add-String_Number_String_link|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:add-String_Number_String_link|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:add-String_Number_String_sam|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:add-String_Number_String_sam|trigger=none}{code:language=javascript}
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders')
/** @type {QBJoin<db:/example_data/order_details>} */
var join = query.joins.add('db:/example_data/order_details', JSRelation.INNER_JOIN, 'odetail')
join.on.add(join.columns.orderid.eq(query.columns.orderid))
query.where.add(join.columns.quantity.le(10))
foundset.loadRecords(query)
{code}{sub-section}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{tbody:id=add-String_String|class=node}{tr:id=name}{td}h6.add{td}{tr}{tr:id=sig}{td}{span:style=float: left; margin-right: 5px;}[QBJoin]{span}{span:id=iets|style=float: left; font-weight: bold;}add{span}{span:id=iets|style=float: left;}\(dataSourceOrRelation, alias){span}{td}{tr}{tr:id=des}{td}{sub-section:add-String_String_des|text=|trigger=button}{sub-section}{sub-section:add-String_String_des|trigger=none|class=sIndent}
prs
Parameters
sIndentdataSourcedata sourcejoinTypejoin type, one of IQueryBuilderJoin#LEFT_OUTER_JOIN, IQueryBuilderJoin#INNER_JOIN, IQueryBuilderJoin#RIGHT_OUTER_JOIN, IQueryBuilderJoin#FULL_JOINaliasthe alias for joining tableret
Returns
sIndentclients
Supported Clients
sIndentSmartClient,WebClient,NGClientsam
Sample
sIdentjavascriptlastDetailRowaddname
add(dataSourceOrRelation, alias)
dessIndent
Add a join based on relation or add a manual join.

<p>When
When dataSourceOrRelation is a relation name, a join will be added based on the relation.

<br>When
When dataSourceOrRelation is a data source, an empty join will be added with join type IQueryBuilderJoin#LEFT
\
_OUTER
\
_JOIN.
{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=snc}{td}*Since*\\{sub-section:add-String_String_snc|text=|trigger=button}{sub-section}{sub-section:add-String_String_snc|trigger=none|class=sIndent} Replace with version info{sub-section}{td}{tr}{builder-show}{tr:id=prs}{td}*Parameters*\\{sub-section:add-String_String_prs|text=|trigger=button}{sub-section}{sub-section:add-String_String_prs|trigger=none|class=sIndent}\{[String]} dataSourceOrRelation -- data source
\{[String]} alias -- the alias for joining table
{sub-section}{td}{tr}{tr:id=ret}{td}*Returns*\\{sub-section:add-String_String_ret|text=|trigger=button}{sub-section}{sub-section:add-String_String_ret|trigger=none|class=sIndent}[QBJoin]{sub-section}{td}{tr}{builder-show:permission=edit}{tr:id=see}{td}*Also see*\\{sub-section:add-String_String_see|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:add-String_String_see|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{builder-show:permission=edit}{tr:id=link}{td}*External links*\\{sub-section:add-String_String_link|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:add-String_String_link|trigger=none}{sub-section}{div}{td}{tr}{builder-show}{tr:id=sam}{td}*Sample*\\{sub-section:add-String_String_sam|text=|trigger=button}{sub-section}{div:class=sIndent}{sub-section:add-String_String_sam|trigger=none}{code:language=javascript}
/** @type {QBSelect<db:/example_data/orders>} */
var query = databaseManager.createSelect('db:/example_data/orders')
/** @type {QBJoin<db:/example_data/order_details>} */
var join = query.joins.add('db:/example_data/order_details', JSRelation.INNER_JOIN, 'odetail')
join.on.add(join.columns.orderid.eq(query.columns.orderid))
query.where.add(join.columns.quantity.le(10))
foundset.loadRecords(query)
{code}{sub-section}{div}{td}{tr}{tr:class=lastDetailRow}{td}{td}{tr}{tbody}{table}
prs
Parameters
sIndentdataSourceOrRelationdata sourcealiasthe alias for joining tableret
Returns
sIndentclients
Supported Clients
sIndentSmartClient,WebClient,NGClientsam
Sample
sIdentjavascriptlastDetailRowgetJoinsname
getJoins()
dessIndentret
Returns
sIndentclients
Supported Clients
sIndentSmartClient,WebClient,NGClientsam
Sample
sIdentjavascriptlastDetailRowremoveUnusedname
removeUnused(keepInnerjoins)
dessIndent
Remove the joins that are not used anywhere in the query.
prs
Parameters
sIndentkeepInnerjoinswhen true inner joins are not removed, inner joins may impact the query result, even when not usedret
Returns
sIndentclients
Supported Clients
sIndentSmartClient,WebClient,NGClientsam
Sample
sIdentjavascriptlastDetailRow