QBFunction

Properties Summarized

TypeNameSummary

Create abs(column) expression

Create an ascending sort expression

Create an aggregate average expression.

Create bit_length(column) expression

Create ceil(column) expression

Create an aggregate count expression.

Extract day from date

Create an descending sort expression

Create floor(column) expression

Extract hour from date

Compare column with null.

Create length(column) expression

Create lower(column) expression

Create an aggregate max expression.

Create an aggregate min expression.

Extract minute from date

Extract month from date

Create a negated condition.

Get query builder parent table clause, this may be a query or a join clause.

Get query builder parent.

Create round(column) expression

Extract second from date

Create sqrt(column) expression

Create an aggregate sum expression.

Create trim(column) expression

Create upper(column) expression

Extract year from date

Methods Summarized

TypeNameSummary

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

Create cast(column, type) expression

Concatename with value

Divide by value

Compare column with a value or another column.

Compare column with a value or another column.

The flags are a bit pattern consisting of 1 or more of the following bits: - JSColumn.

Column type as a string

Compare column with a value or another column.

Compare column with subquery result.

Compare column with values.

Compare column with custom query result.

Compare column with a value or another column.

Compare column with a value or another column.

Compare column with a value or another column.

Create locate(arg) expression

Create locate(arg, start) expression

Compare column with a value or another column.

Subtract value

Create mod(arg) expression

Multiply with value

Create nullif(arg) expression

Add up value

Create substring(pos) expression

Create substring(pos, len) expression

Properties Detailed

abs

Create abs(column) expression

Type QBColumn

Sample

query.result.add(query.columns.custname.abs)

asc

Create an ascending sort expression

Type QBSort

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)

avg

Create an aggregate average expression.

Type QBColumn

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.avg.eq(1))
	foundset.loadRecords(query)

bit_length

Create bit_length(column) expression

Type QBColumn

Sample

query.result.add(query.columns.custname.bit_length)

ceil

Create ceil(column) expression

Type QBColumn

Sample

query.result.add(query.columns.mycol.ceil)

count

Create an aggregate count expression.

Type QBColumn

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)

day

Extract day from date

Type QBColumn

Sample

query.result.add(query.columns.mydatecol.day)

desc

Create an descending sort expression

Type QBSort

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)

floor

Create floor(column) expression

Type QBColumn

Sample

query.result.add(query.columns.mycol.floor)

hour

Extract hour from date

Type QBColumn

Sample

query.result.add(query.columns.mydatecol.hour)

isNull

Compare column with null.

Type QBCondition

Sample

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

len

Create length(column) expression

Type QBColumn

Sample

query.result.add(query.columns.custname.len)

lower

Create lower(column) expression

Type QBColumn

Sample

query.result.add(query.columns.custname.lower)

max

Create an aggregate max expression.

Type QBColumn

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

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)

minute

Extract minute from date

Type QBColumn

Sample

query.result.add(query.columns.mydatecol.minute)

month

Extract month from date

Type QBColumn

Sample

query.result.add(query.columns.mydatecol.month)

not

Create a negated condition.

Type QBColumn

Sample

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

parent

Get query builder parent table clause, this may be a query or a join clause.

Type QBTableClause

Sample

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

Sample

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)

round

Create round(column) expression

Type QBColumn

Sample

query.result.add(query.columns.mycol.round)

second

Extract second from date

Type QBColumn

Sample

query.result.add(query.columns.mydatecol.second)

sqrt

Create sqrt(column) expression

Type QBColumn

Sample

query.result.add(query.columns.custname.sqrt)

sum

Create an aggregate sum expression.

Type QBColumn

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.sum(10))
	foundset.loadRecords(query)

trim

Create trim(column) expression

Type QBColumn

Sample

query.result.add(query.columns.custname.trim)

upper

Create upper(column) expression

Type QBColumn

Sample

query.result.add(query.columns.custname.upper)

year

Extract year from date

Type QBColumn

Sample

query.result.add(query.columns.mydatecol.year)

Methods Detailed

between(value1, value2)

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

Parameters

Returns: QBCondition

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

Sample

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

concat(arg)

Concatename with value

Parameters

  • Object arg valeu to concatenate with

Returns: QBColumn

Sample

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

divide(arg)

Divide by value

Parameters

Returns: QBColumn

Sample

query.result.add(query.columns.mycol.divide(2))

eq(value)

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

Parameters

Returns: QBCondition

Sample

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

ge(value)

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

Parameters

Returns: QBCondition

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

getTypeAsString()

Column type as a string

Returns: String

gt(value)

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

Parameters

Returns: QBCondition

Sample

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

isin(query)

Compare column with subquery result.

Parameters

Returns: QBCondition

Sample

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

isin(values)

Compare column with values.

Parameters

  • Array values array of values

Returns: QBCondition

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

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

Returns: QBCondition

Sample

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

like(pattern)

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

Parameters

  • Object pattern the string value of the pattern

Returns: QBCondition

Sample

query.where.add(query.columns.companyname.like('Serv%'))

// case-insensitive compares can be done using the upper (or lower) functions,
// this can be useful when using for example German letters like ß,
query.where.add(query.columns.companyname.upper.like(query.functions.upper('groß%')))

like(pattern, escape)

Compare column with a value or another column. Operator: like, with escape character

Parameters

  • Object pattern the string value of the pattern

  • Number escape the escape char

Returns: QBCondition

Sample

query.where.add(query.columns.companyname.like('X_%', '_'))

locate(arg)

Create locate(arg) expression

Parameters

Returns: QBColumn

Sample

query.result.add(query.columns.mycol.locate('sample'))

locate(arg, start)

Create locate(arg, start) expression

Parameters

Returns: QBColumn

Sample

query.result.add(query.columns.mycol.locate('sample', 5))

lt(value)

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

Parameters

Returns: QBCondition

Sample

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

minus(arg)

Subtract value

Parameters

Returns: QBColumn

Sample

query.result.add(query.columns.mycol.minus(2))

mod(arg)

Create mod(arg) expression

Parameters

Returns: QBColumn

Sample

query.result.add(query.columns.mycol.mod(2))

multiply(arg)

Multiply with value

Parameters

  • Object arg nr to multiply with

Returns: QBColumn

Sample

query.result.add(query.columns.mycol.multiply(2))

nullif(arg)

Create nullif(arg) expression

Parameters

Returns: QBColumn

Sample

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

plus(arg)

Add up value

Parameters

Returns: QBColumn

Sample

query.result.add(query.columns.mycol.plus(2))

substring(pos)

Create substring(pos) expression

Parameters

Returns: QBColumn

Sample

query.result.add(query.columns.mycol.substring(3))

substring(pos, len)

Create substring(pos, len) expression

Parameters

Returns: QBColumn

Sample

query.result.add(query.columns.mycol.substring(3, 2))

Last updated