QBJoins

Overview

The QBJoins class is a utility object designed to manage and configure all join operations in the QBSelect query builder framework. It provides mechanisms to dynamically add, modify, and remove joins based on data sources, relations, or subqueries. This flexibility allows developers to construct queries that incorporate complex table relationships.

The class supports multiple join types, including INNER JOIN and LEFT OUTER JOIN, and offers tools to manage unused joins, ensuring optimized query execution. It also integrates with parent and root query references, allowing for scalable and modular query design.

For additional guidance on query construction and execution, see the QBSelect documentation.

Properties Summarized

Type	Name	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 Summarized

Type	Name	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 QBJoin.
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.

Properties Detailed

parent

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

Type QBTableClause the parent table clause for these joins.

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.

Type QBSelect the root query builder for these joins.

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 Detailed

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: QBJoin a QBJoin object representing the join added for the specified subquery.

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(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: QBJoin a QBJoin object representing the join added for the specified subquery with the given alias.

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 QBJoin.LEFT_OUTER_JOIN and no alias for the joining table.

Parameters

• String dataSource data source

Returns: QBJoin a QBJoin object representing the newly added join with a default join type.

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: QBJoin a QBJoin object representing the newly added join.

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 QBJoin.LEFT_OUTER_JOIN, QBJoin.INNER_JOIN, QBJoin.RIGHT_OUTER_JOIN, QBJoin.FULL_JOIN

• String alias the alias for joining table

Returns: QBJoin a QBJoin object representing the newly added join with the specified type and alias.

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 QBJoin.LEFT_OUTER_JOIN.

Parameters

• String dataSourceOrRelation data source

• String alias the alias for joining table

Returns: QBJoin a QBJoin object representing the newly added join using the given alias.

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: Array an array of QBJoin objects representing all joins in this query.

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: QBJoins this QBJoins instance after removing unused joins.

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)

---