# QBColumn

## Overview

The `QBColumn` class represents a column in a `QBSelect` query. It is used to define conditions, aggregate functions, and transformations within queries. The class provides a range of properties and methods for handling column-specific operations such as mathematical expressions, sorting, and conditional comparisons.

Columns are typed based on their definition in the database, in situations where the type is unknown a [QBGenericColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbgenericcolumn) is returned by the methods. That class contains all methods and properties that are defined in any of the type-specific column classes.

Key properties include `abs`, `ceil`, and `floor` for mathematical transformations, and `avg`, `count`, and `sum` for aggregate functions. Sorting can be applied using `asc` and `desc`, while date manipulations are supported with properties like `day`, `month`, and `year`.

Methods enable additional functionality, including comparisons (`eq`, `between`, `like`), mathematical operations (`plus`, `minus`, `mod`), and string manipulations (`substring`, `concat`). The `parent` and `root` properties provide access to the query's structure, supporting complex query building and integration.

For more information about constructing and executing queries, refer to the [QBSelect](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbselect) section of this documentation.

## Properties Summarized

| Type                                                                                                           | Name              | Summary                               |
| -------------------------------------------------------------------------------------------------------------- | ----------------- | ------------------------------------- |
| [QBSort](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbsort)                         | [asc](#asc)       | Create an ascending sort expression   |
| [QBCountAggregate](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcountaggregate)     | [count](#count)   | Create an aggregate count expression. |
| [QBSort](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbsort)                         | [desc](#desc)     | Create a descending sort expression   |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition)               | [isNull](#isnull) | Compare column with null.             |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)                     | [max](#max)       | Create an aggregate max expression.   |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)                     | [min](#min)       | Create an aggregate min expression.   |
| [QBColumnComparable](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumncomparable) | [not](#not)       | Create a negated condition.           |

## Methods Summarized

| Type                                                                                                     | Name                                              | Summary                                                                                 |
| -------------------------------------------------------------------------------------------------------- | ------------------------------------------------- | --------------------------------------------------------------------------------------- |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition)         | [between(value1, value2)](#between-value1-value2) | Compare column to a range of 2 values or other columns.                                 |
| [QBGenericColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbgenericcolumn) | [cast(type)](#cast-type)                          | Create cast(column, type) expression                                                    |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)               | [coalesce(value)](#coalesce-value)                | Create coalesce(arg) expression                                                         |
| [QBTextColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtextcolumn)       | [concat(value)](#concat-value)                    | Concatenate with value                                                                  |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition)         | [eq(value)](#eq-value)                            | Compare column with a value or another column.                                          |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition)         | [ge(value)](#ge-value)                            | Compare column with a value or another column.                                          |
| [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number)                             | [getFlags()](#getflags)                           | The flags are a bit pattern consisting of 1 or more of the following bits:  - JSColumn. |
| [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string)                             | [getTypeAsString()](#gettypeasstring)             | Column type as a string                                                                 |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition)         | [gt(value)](#gt-value)                            | Compare column with a value or another column.                                          |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition)         | [isin(query)](#isin-query)                        | Compare column with subquery result.                                                    |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition)         | [isin(values)](#isin-values)                      | Compare column with values.                                                             |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition)         | [isin(customQuery, args)](#isin-customquery-args) | Compare column with custom query result.                                                |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition)         | [le(value)](#le-value)                            | Compare column with a value or another column.                                          |
| [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition)         | [lt(value)](#lt-value)                            | Compare column with a value or another column.                                          |
| [QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn)               | [nullif(arg)](#nullif-arg)                        | Create nullif(arg) expression                                                           |

## Properties Detailed

### asc

Create an ascending sort expression

**Type**\
[QBSort](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbsort) a QBSort representing an ascending sort order.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.sort
.add(query.joins.orders_to_order_details.columns.quantity.asc)
.add(query.columns.companyid)
foundset.loadRecords(query)
```

### count

Create an aggregate count expression.

**Type**\
[QBCountAggregate](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcountaggregate) a QBAggregate representing the count aggregate function.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
	query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries
	.root.having.add(query.joins.orders_to_order_details.columns.quantity.count.eq(0))
	foundset.loadRecords(query)
```

### desc

Create a descending sort expression

**Type**\
[QBSort](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbsort) a QBSort representing a descending sort order.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
query.sort
.add(query.joins.orders_to_order_details.columns.quantity.desc)
.add(query.columns.companyid)
foundset.loadRecords(query)
```

### isNull

Compare column with null.

**Type**\
[QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "is null" comparison.

**Sample**

```js
query.where.add(query.columns.flag.isNull)
```

### max

Create an aggregate max expression.

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBColumn representing the sum aggregate function.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
	query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries
	.root.having.add(query.joins.orders_to_order_details.columns.quantity.count.max(10))
	foundset.loadRecords(query)
```

### min

Create an aggregate min expression.

**Type**\
[QBColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumn) a QBColumn representing the minimum aggregate function.

**Sample**

```js
var query = datasources.db.example_data.orders.createSelect();
	query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries
	.root.having.add(query.joins.orders_to_order_details.columns.quantity.count.min(10))
	foundset.loadRecords(query)
```

### not

Create a negated condition.

**Type**\
[QBColumnComparable](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcolumncomparable) a QBColumn representing the negated condition.

**Sample**

```js
query.where.add(query.columns.flag.not.eq(1))
```

## Methods Detailed

### between(value1, value2)

Compare column to a range of 2 values or other columns.

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value1** ;
* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value2** ;

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "between" comparison for the two values.

**Sample**

```js
query.where.add(query.columns.flag.between(0, 5))
```

### cast(type)

Create cast(column, type) expression

**Parameters**

* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **type** string type, see QUERY\_COLUMN\_TYPES

**Returns:** [QBGenericColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbgenericcolumn) a QBGenericColumn representing the column cast to the specified type.

**Sample**

```js
query.result.add(query.columns.mycol.cast(QUERY_COLUMN_TYPES.TYPE_INTEGER))
```

### coalesce(value)

Create coalesce(arg) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** when column is null

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

**Sample**

```js
query.result.add(query.columns.mycol.coalesce('defval'))
```

### concat(value)

Concatenate with value

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** value to concatenate with

**Returns:** [QBTextColumn](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbtextcolumn) a QBTextColumn representing the column concatenated with the value.

**Sample**

```js
query.result.add(query.columns.firstname.concat(' ').concat(query.columns.lastname))
```

### eq(value)

Compare column with a value or another column.\
Operator: equals

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "equals" comparison.

**Sample**

```js
query.where.add(query.columns.flag.eq(1))
```

### ge(value)

Compare column with a value or another column.\
Operator: greaterThanOrEqual

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "greater than or equal to" comparison.

**Sample**

```js
query.where.add(query.columns.flag.ge(2))
```

### getFlags()

The flags are a bit pattern consisting of 1 or more of the following bits:\
 \- JSColumn.UUID\_COLUMN\
 \- JSColumn.EXCLUDED\_COLUMN\
 \- JSColumn.TENANT\_COLUMN

**Returns:** [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) an integer representing the flags of the column.

### getTypeAsString()

Column type as a string

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

### gt(value)

Compare column with a value or another column.\
Operator: greaterThan

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "greater than" comparison.

**Sample**

```js
query.where.add(query.columns.flag.gt(0))
```

### isin(query)

Compare column with subquery result.

**Parameters**

* [QBPart](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbpart) **query** subquery

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "in" comparison with a subquery.

**Sample**

```js
query.where.add(query.columns.flag.isin(query2))
```

### isin(values)

Compare column with values.

**Parameters**

* [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **values** array of values

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "in" comparison with a list of values.

**Sample**

```js
query.where.add(query.columns.flag.isin([1, 5, 99]))
```

### isin(customQuery, args)

Compare column with custom query result.

**Parameters**

* [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **customQuery** custom query
* [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) **args** query arguments

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "in" comparison with a custom query and arguments.

**Sample**

```js
query.where.add(query.columns.ccy.isin("select ccycode from currencies c where c.category = " + query.getTableAlias() + ".currency_category and c.flag = ?", ['T']))
```

### le(value)

Compare column with a value or another column.\
Operator: lessThanOrEqual

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "less than or equal to" comparison.

**Sample**

```js
query.where.add(query.columns.flag.le(2))
```

### lt(value)

Compare column with a value or another column.\
Operator: lessThan

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **value** ;

**Returns:** [QBCondition](https://docs.servoy.com/reference/servoycore/dev-api/database-manager/qbcondition) a QBCondition representing the "less than" comparison.

**Sample**

```js
query.where.add(query.columns.flag.lt(99))
```

### nullif(arg)

Create nullif(arg) expression

**Parameters**

* [Object](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/object) **arg** object to compare

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

**Sample**

```js
query.result.add(query.columns.mycol.nullif('none'))
```

***