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 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 section of this documentation.

Properties Summarized

Type	Name	Summary
QBSort	asc	Create an ascending sort expression
QBCountAggregate	count	Create an aggregate count expression.
QBSort	desc	Create a descending sort expression
QBCondition	isNull	Compare column with null.
QBColumn	max	Create an aggregate max expression.
QBColumn	min	Create an aggregate min expression.
QBColumnComparable	not	Create a negated condition.

Methods Summarized

Type	Name	Summary
QBCondition	between(value1, value2)	Compare column to a range of 2 values or other columns.
QBGenericColumn	cast(type)	Create cast(column, type) expression
QBColumn	coalesce(value)	Create coalesce(arg) expression
QBTextColumn	concat(value)	Concatenate with value
QBCondition	eq(value)	Compare column with a value or another column.
QBCondition	ge(value)	Compare column with a value or another column.
Number	getFlags()	The flags are a bit pattern consisting of 1 or more of the following bits: - JSColumn.
String	getTypeAsString()	Column type as a string
QBCondition	gt(value)	Compare column with a value or another column.
QBCondition	isin(query)	Compare column with subquery result.
QBCondition	isin(values)	Compare column with values.
QBCondition	isin(customQuery, args)	Compare column with custom query result.
QBCondition	le(value)	Compare column with a value or another column.
QBCondition	lt(value)	Compare column with a value or another column.
QBColumn	nullif(arg)	Create nullif(arg) expression

Properties Detailed

asc

Create an ascending sort expression

Type QBSort a QBSort representing an ascending sort order.

Sample

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 a QBAggregate representing the count aggregate function.

Sample

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 a QBSort representing a descending sort order.

Sample

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 a QBCondition representing the "is null" comparison.

Sample

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

max

Create an aggregate max expression.

Type QBColumn a QBColumn representing the sum aggregate function.

Sample

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 a QBColumn representing the minimum aggregate function.

Sample

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 a QBColumn representing the negated condition.

Sample

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 value1 ;

• Object value2 ;

Returns: QBCondition a QBCondition representing the "between" comparison for the two values.

Sample

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

cast(type)

Create cast(column, type) expression

Parameters

• String type string type, see QUERY_COLUMN_TYPES

Returns: QBGenericColumn a QBGenericColumn representing the column cast to the specified type.

Sample

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

coalesce(value)

Create coalesce(arg) expression

Parameters

• Object value when column is null

Returns: QBColumn a QBColumn representing the coalesce expression.

Sample

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

concat(value)

Concatenate with value

Parameters

• Object value value to concatenate with

Returns: QBTextColumn a QBTextColumn representing the column concatenated with the value.

Sample

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 value ;

Returns: QBCondition a QBCondition representing the "equals" comparison.

Sample

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

ge(value)

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

Parameters

• Object value ;

Returns: QBCondition a QBCondition representing the "greater than or equal to" comparison.

Sample

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 an integer representing the flags of the column.

getTypeAsString()

Column type as a string

Returns: String a string representing the column type.

gt(value)

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

Parameters

• Object value ;

Returns: QBCondition a QBCondition representing the "greater than" comparison.

Sample

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

isin(query)

Compare column with subquery result.

Parameters

• QBPart query subquery

Returns: QBCondition a QBCondition representing the "in" comparison with a subquery.

Sample

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

isin(values)

Compare column with values.

Parameters

• Array values array of values

Returns: QBCondition a QBCondition representing the "in" comparison with a list of values.

Sample

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

isin(customQuery, args)

Compare column with custom query result.

Parameters

• String customQuery custom query

• Array args query arguments

Returns: QBCondition a QBCondition representing the "in" comparison with a custom query and arguments.

Sample

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 value ;

Returns: QBCondition a QBCondition representing the "less than or equal to" comparison.

Sample

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

lt(value)

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

Parameters

• Object value ;

Returns: QBCondition a QBCondition representing the "less than" comparison.

Sample

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

nullif(arg)

Create nullif(arg) expression

Parameters

• Object arg object to compare

Returns: QBColumn a QBColumn representing the nullif expression.

Sample

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

---