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
Methods Summarized
The flags are a bit pattern consisting of 1 or more of the following bits: - JSColumn.
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 QBColumn 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
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: QBColumn 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: QBColumn 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
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'))Last updated
Was this helpful?