Child pages
  • QBFunction
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 18 Next »

Refresh page Mar 28, 2024 23:36

Supported Clients
SmartClient WebClient NGClient

Property Summary
QBFunction abs Create abs(column) expression
QBSort asc Create an ascending sort expression
QBAggregate avg Create an aggregate expression.
QBFunction bit_length Create bit_length(column) expression
QBFunction ceil Create ceil(column) expression
QBAggregate count Create an aggregate expression.
QBFunction day Extract day from date
QBSort desc Create an descending sort expression
QBFunction floor Create floor(column) expression
QBFunction hour Extract hour from date
QBCondition isNull Compare column with null.
QBFunction len Create length(column) expression
QBFunction lower Create lower(column) expression
QBAggregate max Create an aggregate expression.
QBAggregate min Create an aggregate expression.
QBFunction minute Extract minute from date
QBFunction 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.
QBFunction round Create round(column) expression
QBFunction second Extract second from date
QBFunction sqrt Create sqrt(column) expression
QBAggregate sum Create an aggregate expression.
QBFunction trim Create trim(column) expression
QBFunction upper Create upper(column) expression
QBFunction year Extract year from date

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

Property Details

abs

Create abs(column) expression

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

asc

Create an ascending sort expression

Returns

Supported Clients

SmartClient,WebClient,NGClient

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 expression.

Returns

Supported Clients

SmartClient,WebClient,NGClient

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

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

ceil

Create ceil(column) expression

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

count

Create an aggregate expression.

Returns

Supported Clients

SmartClient,WebClient,NGClient

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

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

desc

Create an descending sort expression

Returns

Supported Clients

SmartClient,WebClient,NGClient

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

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

hour

Extract hour from date

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

isNull

Compare column with null.

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

len

Create length(column) expression

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

lower

Create lower(column) expression

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

max

Create an aggregate expression.

Returns

Supported Clients

SmartClient,WebClient,NGClient

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 expression.

Returns

Supported Clients

SmartClient,WebClient,NGClient

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

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

month

Extract month from date

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

not

Create a negated condition.

Returns

Supported Clients

SmartClient,WebClient,NGClient

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.

Returns

Supported Clients

SmartClient,WebClient,NGClient

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.

Returns

Supported Clients

SmartClient,WebClient,NGClient

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

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

second

Extract second from date

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

sqrt

Create sqrt(column) expression

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

sum

Create an aggregate expression.

Returns

Supported Clients

SmartClient,WebClient,NGClient

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

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

upper

Create upper(column) expression

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

year

Extract year from date

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

Methods Details

between(value1, value2)

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

Parameters

Object value1 ;
Object value2 ;

Returns

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

gt(value)

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

Parameters

Object value ;

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

isin(query)

Compare column with subquery result.

Parameters

QBPart query subquery

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

isin(values)

Compare column with values.

Parameters

Array values array of values

Returns

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

like(pattern)

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

Parameters

String pattern the string value of the pattern

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

like(pattern, escape)

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

Parameters

String pattern the string value of the pattern
Number escape the escape char

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

locate(arg)

Create locate(arg) expression

Parameters

Object arg string to locate

Returns

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

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

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

minus(arg)

Subtract value

Parameters

Object arg nr to subtract

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

mod(arg)

Create mod(arg) expression

Parameters

Object arg mod arg

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

multiply(arg)

Multiply with value

Parameters

Object arg nr to multiply with

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

nullif(arg)

Create nullif(arg) expression

Parameters

Object arg object to compare

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

plus(arg)

Add up value

Parameters

Object arg nr to add

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

substring(pos)

Create substring(pos) expression

Parameters

Number pos ;

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

substring(pos, len)

Create substring(pos, len) expression

Parameters

Number pos ;
Number len ;

Returns

Supported Clients

SmartClient,WebClient,NGClient

Sample

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

  • No labels