# 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](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect).

## Properties Summarized

| Type                                                                                                 | Name              | Summary                                                                      |
| ---------------------------------------------------------------------------------------------------- | ----------------- | ---------------------------------------------------------------------------- |
| [QBTableClause](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtableclause) | [parent](#parent) | Get query builder parent table clause, this may be a query or a join clause. |
| [QBSelect](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect)           | [root](#root)     | Get query builder parent.                                                    |

## Methods Summarized

| Type                                                                                     | Name                                                                         | Summary                                                                                         |
| ---------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------- |
| [QBJoin](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbjoin)   | [add(subqueryBuilder, joinType)](#add-subquerybuilder-jointype)              | Add a join clause from the parent query builder part to a derived table based on another query. |
| [QBJoin](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbjoin)   | [add(subqueryBuilder, joinType, alias)](#add-subquerybuilder-jointype-alias) | Add a join clause from the parent query builder part to a derived table based on another query. |
| [QBJoin](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbjoin)   | [add(dataSource)](#add-datasource)                                           | Add a join with join type QBJoin.                                                               |
| [QBJoin](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbjoin)   | [add(dataSource, joinType)](#add-datasource-jointype)                        | Add a join with no alias for the joining table.                                                 |
| [QBJoin](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbjoin)   | [add(dataSource, joinType, alias)](#add-datasource-jointype-alias)           | Add a join clause from the parent query builder part to the specified data source.              |
| [QBJoin](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbjoin)   | [add(dataSourceOrRelation, alias)](#add-datasourceorrelation-alias)          | Add a join based on relation or add a manual join.                                              |
| [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array)               | [getJoins()](#getjoins)                                                      |                                                                                                 |
| [QBJoins](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbjoins) | [removeUnused(keepInnerjoins)](#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](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtableclause) the parent table clause for these joins.

**Sample**

```js
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](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect) the root query builder for these joins.

**Sample**

```js
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](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect) **subqueryBuilder** ;
* [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **joinType** ;

**Returns:** [QBJoin](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbjoin) a QBJoin object representing the join added for the specified subquery.

**Sample**

```js
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](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect) **subqueryBuilder** ;
* [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **joinType** ;
* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **alias** ;

**Returns:** [QBJoin](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbjoin) a QBJoin object representing the join added for the specified subquery with the given alias.

**Sample**

```js
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](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **dataSource** data source

**Returns:** [QBJoin](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbjoin) a QBJoin object representing the newly added join with a default join type.

**Sample**

```js
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](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **dataSource** data source
* [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **joinType** join type, one of QBJoin.LEFT\_OUTER\_JOIN, QBJoin.INNER\_JOIN, QBJoin.RIGHT\_OUTER\_JOIN, QBJoin.FULL\_JOIN

**Returns:** [QBJoin](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbjoin) a QBJoin object representing the newly added join.

**Sample**

```js
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](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **dataSource** data source
* [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **joinType** join type, one of QBJoin.LEFT\_OUTER\_JOIN, QBJoin.INNER\_JOIN, QBJoin.RIGHT\_OUTER\_JOIN, QBJoin.FULL\_JOIN
* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **alias** the alias for joining table

**Returns:** [QBJoin](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbjoin) a QBJoin object representing the newly added join with the specified type and alias.

**Sample**

```js
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](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **dataSourceOrRelation** data source
* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **alias** the alias for joining table

**Returns:** [QBJoin](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbjoin) a QBJoin object representing the newly added join using the given alias.

**Sample**

```js
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](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/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](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) **keepInnerjoins** when true inner joins are not removed, inner joins may impact the query result, even when not used

**Returns:** [QBJoins](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbjoins) this QBJoins instance after removing unused joins.

**Sample**

```js
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)
```

***