# QBJoin

## Overview

The `QBJoin` class simplifies the process of creating and managing SQL joins within the `QBSelect` query builder framework. It provides tools to handle complex relationships between tables, enabling construction of queries with various join types, such as `LEFT OUTER JOIN` or `INNER JOIN`. By leveraging the class, developers can define join conditions and access columns from related tables for select or where clauses.

The class supports dynamic query building with its integration of parent and root query references, ensuring flexibility and scalability in query design. It also allows for annotating joins with comments, aiding in query documentation and readability.

For further details on constructing and executing queries, refer to the [QBSelect documentation](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect).

## Properties Summarized

| Type                                                                                                           | Name                  | Summary                                                                                        |
| -------------------------------------------------------------------------------------------------------------- | --------------------- | ---------------------------------------------------------------------------------------------- |
| [QBColumns](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumns)                   | [columns](#columns)   | Get all the columns of the datasource that can be used for this query (select or where clause) |
| [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string)                                   | [comment](#comment)   | Specifies a comment of the join.                                                               |
| [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number)                                   | [joinType](#jointype) | Returns the join type, one of QBJoin.                                                          |
| [QBJoins](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbjoins)                       | [joins](#joins)       | Get the joins clause of this table based clause.                                               |
| [QBLogicalCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qblogicalcondition) | [on](#on)             | Get the on clause for the join.                                                                |
| [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                                       |
| ------------------------------------------------------------------------------------------ | --------------------------------------------------------------------- | --------------------------------------------- |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) | [getColumn(name)](#getcolumn-name)                                    | Get a column from the table.                  |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) | [getColumn(columnTableAlias, name)](#getcolumn-columntablealias-name) | Get a column from the table with given alias. |
| [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string)               | [getDataSource()](#getdatasource)                                     | Returns the datasource for this.              |
| [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string)               | [getTableAlias()](#gettablealias)                                     | Returns the table alias for this.             |

## Properties Detailed

### columns

Get all the columns of the datasource that can be used for this query (select or where clause)

**Type**\
[QBColumns](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumns)

**Sample**

```js
var query = foundset.getQuery();
query.result.add(query.columns.name, "name");
query.where.add(query.columns.orderdate.isNull)
```

### comment

Specifies a comment of the join.

**Type**\
[String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) the comment of the join.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.result.add(query.joins.orders_to_orderlines.columns.price)
query.joins.orders_to_orderlines.comment = 'Join comment'
```

### joinType

Returns the join type, one of QBJoin.LEFT\_OUTER\_JOIN, QBJoin.INNER\_JOIN, QBJoin.RIGHT\_OUTER\_JOIN, QBJoin.FULL\_JOIN

**Type**\
[Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) the join type, one of IQueryBuilderJoin

### joins

Get the joins clause of this table based clause.\
Joins added to this clause will be based on this table clauses table.

**Type**\
[QBJoins](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbjoins)

**Sample**

```js
foundset.getQuery().joins
```

### on

Get the on clause for the join.

**Type**\
[QBLogicalCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qblogicalcondition) the logical condition for the join.

**Sample**

```js
var query = datasources.db.example_data.person.createSelect();
/** @type {QBJoin<db:/example_data/person>} */
var join1 = query.joins.add('db:/example_data/person')
join1.on.add(query.columns.parent_person_id.eq(join1.columns.person_id))
/** @type {QBJoin<db:/example_data/person>} */
var join2 = query.joins.add('db:/example_data/person')
join2.on.add(join1.columns.parent_person_id.eq(join2.columns.person_id))

query.where.add(join2.columns.name.eq('john'))
foundset.loadRecords(query)
```

### 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)

**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)

**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

### getColumn(name)

Get a column from the table.

**Parameters**

* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **name** the name of column to get

**Returns:** [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) the QBColumn representing the specified column name.

**Sample**

```js
foundset.getQuery().getColumn('orderid')
```

### getColumn(columnTableAlias, name)

Get a column from the table with given alias.\
The alias may be of the main table or any level deep joined table.

**Parameters**

* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **columnTableAlias** the alias for the table
* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **name** the name of column to get

**Returns:** [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) the QBColumn representing the specified column from the table with the given alias.

**Sample**

```js
foundset.getQuery().getColumn('orderid', 'opk')
```

### getDataSource()

Returns the datasource for this.

**Returns:** [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) the dataSource

### getTableAlias()

Returns the table alias for this.

**Returns:** [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) the tableAlias

***