QBFunction

Overview

The QBFunction class is a wrapper for SQL functions, designed for use within QBSelect to build complex expressions and conditions for queries. It facilitates operations such as mathematical transformations, string manipulations, and date/time calculations, enabling dynamic and flexible data handling.

Key features include creating expressions like abs, avg, ceil, lower, and round for mathematical or string operations. Date extraction functions, such as day, month, and year, are also supported. Logical conditions like between, like, eq, and lt provide powerful tools for data filtering and comparison.

For more information on constructing and executing queries, refer to the QBSelect section of this documentation.

Properties Summarized

Type	Name	Summary
QBColumn	abs	Create abs(column) expression
QBSort	asc	Create an ascending sort expression
QBColumn	avg	Create an aggregate average expression.
QBColumn	bit_length	Create bit_length(column) expression
QBColumn	ceil	Create ceil(column) expression
QBColumn	count	Create an aggregate count expression.
QBColumn	day	Extract day from date
QBSort	desc	Create an descending sort expression
QBColumn	floor	Create floor(column) expression
QBColumn	hour	Extract hour from date
QBCondition	isNull	Compare column with null.
QBColumn	len	Create length(column) expression
QBColumn	lower	Create lower(column) expression
QBColumn	max	Create an aggregate max expression.
QBColumn	min	Create an aggregate min expression.
QBColumn	minute	Extract minute from date
QBColumn	month	Extract month from date
QBColumn	not	Create a negated condition.
QBTableClause	parent	Get query builder parent table clause, this may be a query or a join clause.
QBSelect	root	Get query builder parent.
QBColumn	round	Create round(column) expression
QBColumn	second	Extract second from date
QBColumn	sqrt	Create sqrt(column) expression
QBColumn	sum	Create an aggregate sum expression.
QBColumn	trim	Create trim(column) expression
QBColumn	upper	Create upper(column) expression
QBColumn	year	Extract year from date

Methods Summarized

Type	Name	Summary
QBCondition	between(value1, value2)	Compare column to a range of 2 values or other columns.
QBColumn	cast(type)	Create cast(column, type) expression
QBColumn	concat(arg)	Concatename with value
QBColumn	divide(arg)	Divide by 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	like(pattern)	Compare column with a value or another column.
QBCondition	like(pattern, escape)	Compare column with a value or another column.
QBColumn	locate(arg)	Create locate(arg) expression
QBColumn	locate(arg, start)	Create locate(arg, start) expression
QBCondition	lt(value)	Compare column with a value or another column.
QBColumn	minus(arg)	Subtract value
QBColumn	mod(arg)	Create mod(arg) expression
QBColumn	multiply(arg)	Multiply with value
QBColumn	nullif(arg)	Create nullif(arg) expression
QBColumn	plus(arg)	Add up value
QBColumn	substring(pos)	Create substring(pos) expression
QBColumn	substring(pos, len)	Create substring(pos, len) expression

Properties Detailed

abs

Create abs(column) expression

Type QBColumn a QBFunction representing the absolute value function.

Sample

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

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)

avg

Create an aggregate average expression.

Type QBColumn a QBAggregate representing the average 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.avg.eq(1))
	foundset.loadRecords(query)

bit_length

Create bit_length(column) expression

Type QBColumn a QBFunction representing the bit length function.

Sample

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

ceil

Create ceil(column) expression

Type QBColumn a QBFunction representing the ceil function.

Sample

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

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)

day

Extract day from date

Type QBColumn a QBFunction representing the extraction of the day from a date.

Sample

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

desc

Create an 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)

floor

Create floor(column) expression

Type QBColumn a QBFunction representing the floor function.

Sample

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

hour

Extract hour from date

Type QBColumn a QBFunction representing the extraction of the hour from a date.

Sample

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

isNull

Compare column with null.

Type QBCondition a QBCondition representing the "is null" comparison.

Sample

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

len

Create length(column) expression

Type QBColumn a QBFunction representing the length function.

Sample

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

lower

Create lower(column) expression

Type QBColumn a QBFunction representing the lower case transformation.

Sample

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

max

Create an aggregate max expression.

Type QBColumn a QBAggregate representing the maximum 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 QBAggregate 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)

minute

Extract minute from date

Type QBColumn a QBFunction representing the extraction of the minute from a date.

Sample

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

month

Extract month from date

Type QBColumn a QBFunction representing the extraction of the month from a date.

Sample

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

not

Create a negated condition.

Type QBColumn a QBColumn representing the negated condition.

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 a QBFunction representing the round function.

Sample

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

second

Extract second from date

Type QBColumn a QBFunction representing the extraction of the second from a date.

Sample

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

sqrt

Create sqrt(column) expression

Type QBColumn a QBFunction representing the square root function.

Sample

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

sum

Create an aggregate sum expression.

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

trim

Create trim(column) expression

Type QBColumn a QBFunction representing the trim function.

Sample

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

upper

Create upper(column) expression

Type QBColumn a QBFunction representing the upper case transformation.

Sample

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

year

Extract year from date

Type QBColumn a QBFunction representing the extraction of the year from a date.

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

• 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: QBColumn a QBFunction representing the cast function with the specified type.

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 a QBFunction representing the concatenation operation.

Sample

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

divide(arg)

Divide by value

Parameters

• Object arg nr to divide by

Returns: QBColumn a QBFunction representing the division operation.

Sample

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

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

like(pattern)

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

Parameters

• Object pattern the string value of the pattern

Returns: QBCondition a QBCondition representing the "like" comparison with a pattern.

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 a QBCondition representing the "like" comparison with a pattern and an escape character.

Sample

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

locate(arg)

Create locate(arg) expression

Parameters

• Object arg string to locate

Returns: QBColumn a QBFunction representing the locate function for the specified string.

Sample

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

locate(arg, start)

Create locate(arg, start) expression

Parameters

• Object arg string to locate

• Number start start pos

Returns: QBColumn a QBFunction representing the locate function starting from the specified position.

Sample

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

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

minus(arg)

Subtract value

Parameters

• Object arg nr to subtract

Returns: QBColumn a QBFunction representing the subtraction operation.

Sample

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

mod(arg)

Create mod(arg) expression

Parameters

• Object arg mod arg

Returns: QBColumn a QBFunction representing the modulo operation.

Sample

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

multiply(arg)

Multiply with value

Parameters

• Object arg nr to multiply with

Returns: QBColumn a QBFunction representing the multiplication operation.

Sample

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

nullif(arg)

Create nullif(arg) expression

Parameters

• Object arg object to compare

Returns: QBColumn a QBFunction representing the nullif function.

Sample

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

plus(arg)

Add up value

Parameters

• Object arg nr to add

Returns: QBColumn a QBFunction representing the addition operation.

Sample

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

substring(pos)

Create substring(pos) expression

Parameters

• Number pos ;

Returns: QBColumn a QBFunction representing the substring function starting from the specified position.

Sample

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

substring(pos, len)

Create substring(pos, len) expression

Parameters

• Number pos ;

• Number len ;

Returns: QBColumn a QBFunction representing the substring function with the specified position and length.

Sample

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

---