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
Methods Summarized
Add 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 clause from the parent query builder part to the specified data source.
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
Returns: QBJoin a QBJoin object representing the join added for the specified subquery.
add(subqueryBuilder, joinType, alias)
Add a join clause from the parent query builder part to a derived table based on another query.
Parameters
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 IQueryBuilderJoin#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 IQueryBuilderJoin#LEFT_OUTER_JOIN, IQueryBuilderJoin#INNER_JOIN, IQueryBuilderJoin#RIGHT_OUTER_JOIN, IQueryBuilderJoin#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 IQueryBuilderJoin#LEFT_OUTER_JOIN.
Parameters
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)
Last updated
Was this helpful?