Child pages
  • QBJoins
Skip to end of metadata
Go to start of metadata

Refresh page Mar 16, 2024 16:31

Supported Clients
SmartClient WebClient NGClient

Property Summary
QBTableClause parent Get query builder parent table clause, this may be a query or a join clause.
QBSelect root Get query builder parent.

Methods Summary
QBJoin add(subqueryBuilder, joinType) Add a join clause from the parent query builder part to a derived table based on another query.
QBJoin add(subqueryBuilder, joinType, alias) Add a join clause from the parent query builder part to a derived table based on another query.
QBJoin add(dataSource) Add a join with join type IQueryBuilderJoin#LEFT_OUTER_JOIN and no alias for the joining table.
QBJoin add(dataSource, joinType) Add a join with no alias for the joining table.
QBJoin add(dataSource, joinType, alias) Add a join clause from the parent query builder part to the specified data source.
QBJoin add(dataSourceOrRelation, alias) Add a join based on relation or add a manual join.
Array getJoins()
QBJoins removeUnused(keepInnerjoins) Remove the joins that are not used anywhere in the query.

Property Details

parent

Get query builder parent table clause, this may be a query or a join clause.

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

var query = datasources.db.example_data.person.createSelect();
	query.where.add(query.joins.person_to_parent.joins.person_to_parent.columns.name.eq('john'))
	foundset.loadRecords(query)

root

Get query builder parent.

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

var subquery = datasources.db.example_data.order_details.createSelect();

	var query = datasources.db.example_data.orders.createSelect();
	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)

Methods Details

add(subqueryBuilder, joinType)

Add a join clause from the parent query builder part to a derived table based on another query.

Parameters

QBSelect subqueryBuilder ;
Number joinType ;

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

 

add(subqueryBuilder, joinType, alias)

Add a join clause from the parent query builder part to a derived table based on another query.

Parameters

QBSelect subqueryBuilder ;
Number joinType ;
String alias ;

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

var subquery = datasources.db.example_data.products.createSelect();
 subquery.where.add(subquery.columns.supplierid.eq(99));
 subquery.result.add(subquery.columns.categoryid, 'subcat')
 subquery.result.add(subquery.columns.productid, 'subprod')

 var query = datasources.db.example_data.order_details.createSelect();
 // add a join on a derived table using a subquery
 var join = query.joins.add(subquery, QBJoin.INNER_JOIN, 'subprods');
 join.on.add(query.columns.productid.eq(join.columns['subprod']));
 query.result.add(query.columns.quantity);
 query.result.add(join.columns['subcat']);

add(dataSource)

Add a join with join type IQueryBuilderJoin#LEFT_OUTER_JOIN and no alias for the joining table.

Parameters

String dataSource data source

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

var query = datasources.db.example_data.orders.createSelect();
 /** @type {QBJoin<db:/example_data/order_details>} */
	var join = query.joins.add('db:/example_data/order_details', QBJoin.INNER_JOIN, 'odetail')
	join.on.add(join.columns.orderid.eq(query.columns.orderid))
 // to add a join based on a relation, use the relation name
 var join2 = query.joins.add('orders_to_customers', 'cust')
	query.where.add(join2.columns.customerid.eq(999))
	foundset.loadRecords(query)

add(dataSource, joinType)

Add a join with no alias for the joining table.

Parameters

String dataSource data source
Number joinType join type, one of QBJoin.LEFT_OUTER_JOIN, QBJoin.INNER_JOIN, QBJoin.RIGHT_OUTER_JOIN, QBJoin.FULL_JOIN

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

var query = datasources.db.example_data.orders.createSelect();
 /** @type {QBJoin<db:/example_data/order_details>} */
	var join = query.joins.add('db:/example_data/order_details', QBJoin.INNER_JOIN, 'odetail')
	join.on.add(join.columns.orderid.eq(query.columns.orderid))
 // to add a join based on a relation, use the relation name
 var join2 = query.joins.add('orders_to_customers', 'cust')
	query.where.add(join2.columns.customerid.eq(999))
	foundset.loadRecords(query)

add(dataSource, joinType, alias)

Add a join clause from the parent query builder part to the specified data source.

Parameters

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

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

var query = datasources.db.example_data.orders.createSelect();
 /** @type {QBJoin<db:/example_data/order_details>} */
	var join = query.joins.add('db:/example_data/order_details', QBJoin.INNER_JOIN, 'odetail')
	join.on.add(join.columns.orderid.eq(query.columns.orderid))
 // to add a join based on a relation, use the relation name
 var join2 = query.joins.add('orders_to_customers', 'cust')
	query.where.add(join2.columns.customerid.eq(999))
	foundset.loadRecords(query)

add(dataSourceOrRelation, alias)

Add a join based on relation or add a manual join.
When dataSourceOrRelation is a relation name, a join will be added based on the relation.
When dataSourceOrRelation is a data source, an empty join will be added with join type IQueryBuilderJoin#LEFT_OUTER_JOIN.

Parameters

String dataSourceOrRelation data source
String alias the alias for joining table

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

var query = datasources.db.example_data.orders.createSelect();
 /** @type {QBJoin<db:/example_data/order_details>} */
	var join = query.joins.add('db:/example_data/order_details', QBJoin.INNER_JOIN, 'odetail')
	join.on.add(join.columns.orderid.eq(query.columns.orderid))
 // to add a join based on a relation, use the relation name
 var join2 = query.joins.add('orders_to_customers', 'cust')
	query.where.add(join2.columns.customerid.eq(999))
	foundset.loadRecords(query)

getJoins()

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

 

removeUnused(keepInnerjoins)

Remove the joins that are not used anywhere in the query.

Parameters

Boolean keepInnerjoins when true inner joins are not removed, inner joins may impact the query result, even when not used

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

var query = datasources.db.example_data.orders.createSelect()
 // a joins is added from the relation
 query.sort.add(query.joins.orders_to_detail.columns.price.sum.asc)
 // clearing the sort does not remove the joins
 query.sort.clear()
 // remove the unused joins
 query.joins.removeUnused(false)

  • No labels