Returns Supported Clients Sample Returns Supported Clients Sample Returns Supported Clients Sample Returns Supported Clients Sample Returns Supported Clients Sample Returns Supported Clients Sample Returns Supported Clients Sample Returns Supported Clients Sample Returns Supported Clients Sample Returns Supported Clients Sample Returns Supported Clients Sample Returns Supported Clients Sample Returns Supported Clients Sample Returns Supported Clients Sample Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients Sample Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample Parameters Returns Supported Clients Sample
Mar 12, 2023 02:09Supported Clients
SmartClient
WebClient
NGClient
Property Summary
QBLogicalCondition
and
Create an AND-condition to add conditions to.
QBCase
case
Create an case searched expression.
QBColumns
columns
Get all the columns of the datasource that can be used for this query (select or where clause)
String
comment
Specifies a comment of the query.
QBFunctions
functions
Get the functions clause from a query, used for functions that are not tied to a column.
QBGroupBy
groupBy
Get the group by clause from a query
QBLogicalCondition
having
Get the having-part of the query, used to add conditions.
QBJoins
joins
Get the joins clause of this table based clause.
QBLogicalCondition
or
Create an OR-condition to add conditions to.
QBParameters
params
Get the named parameters from a query.
QBTableClause
parent
Get query builder parent table clause, this may be a query or a join clause.
QBResult
result
Get the result part of the query, used to add result columns or values.
QBSelect
root
Get query builder parent.
QBSorts
sort
Get the sorting part of the query.
QBLogicalCondition
where
Get the where-part of the query, used to add conditions.
Methods Summary
QBSelect
clearHaving()
Clear the having-part of the query.
QBCondition
exists(query)
Get an exists-condition from a subquery
QBColumn
getColumn(name)
Get a column from the table.
QBColumn
getColumn(columnTableAlias, name)
Get a column from the table with given alias.
String
getDataSource()
Returns the datasource for this.
QBParameter
getParameter(name)
Get or create a parameter for the query, this used to parameterize queries.
String
getTableAlias()
Returns the table alias for this.
Object
inline(number)
Create an inlined value.
Object
inline(number, columnForType)
Create an inlined value converted to the type of the column.
Object
inline(string)
Create an inlined (quoted) value.
QBCondition
not(cond)
Create an negated condition.
QBCondition
not(cond)
Create an negated condition.
Property Details
and
Create an AND-condition to add conditions to.
SmartClient,WebClient,NGClient
query.where.add(
query.or
.add(
query.and
.add(query.columns.flag.eq(1))
.add(query.columns.order_date.isNull)
)
.add(
query.and
.add(query.columns.flag.eq(2))
.add(query.column.order_date.gt(new Date()))
)
);
case
Create an case searched expression.
SmartClient,WebClient,NGClient
var query = datasources.db.example_data.order_details.createSelect();
// case expressions can be added to the result of the query
query.result.add(query.case.when(query.columns.quantity.ge(1000)).then('BIG').else('small'));
// they can also be used in conditions
query.where.add(query.case
.when(query.columns.discount.gt(10)).then(50)
.when(query.columns.quantity.le(20)).then(70)
.else(100)
.multiply(query.columns.unitprice).lt(10000));
columns
Get all the columns of the datasource that can be used for this query (select or where clause)
SmartClient,WebClient,NGClient
var query = foundset.getQuery();
query.result.add(query.columns.name, "name");
query.where.add(query.columns.orderdate.isNull)
comment
Specifies a comment of the query.
SmartClient,WebClient,NGClient
var query = datasources.db.example_data.orders.createSelect();
query.comment = 'Query comment'
functions
Get the functions clause from a query, used for functions that are not tied to a column.
SmartClient,WebClient,NGClient
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.upper.eq(query.functions.upper('servoy'))) //$NON-NLS-1$
foundset.loadRecords(query)
groupBy
Get the group by clause from a query
SmartClient,WebClient,NGClient
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)
having
Get the having-part of the query, used to add conditions.
The conditions added here are AND-ed.
SmartClient,WebClient,NGClient
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)
joins
Get the joins clause of this table based clause.
Joins added to this clause will be based on this table clauses table.
SmartClient,WebClient,NGClient
foundset.getQuery().joins
or
Create an OR-condition to add conditions to.
SmartClient,WebClient,NGClient
query.where.add(
query.or
.add(
query.and
.add(query.columns.flag.eq(1))
.add(query.columns.order_date.isNull)
)
.add(
query.and
.add(query.columns.flag.eq(2))
.add(query.column.order_date.gt(new Date()))
)
);
params
Get the named parameters from a query.
SmartClient,WebClient,NGClient
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.contact_id.eq(query.getParameter('mycontactid')))
// load orders where contact_id = 100
query.params['mycontactid'] = 100
foundset.loadRecords(query)
// load orders where contact_id = 200
query.params['mycontactid'] = 200
foundset.loadRecords(query)
parent
Get query builder parent table clause, this may be a query or a join clause.
SmartClient,WebClient,NGClient
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)
result
Get the result part of the query, used to add result columns or values.
SmartClient,WebClient,NGClient
query.result.add(query.columns.company_id).add(query.columns.customerid)
root
Get query builder parent.
SmartClient,WebClient,NGClient
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)
sort
Get the sorting part of the query.
SmartClient,WebClient,NGClient
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)
where
Get the where-part of the query, used to add conditions.
The conditions added here are AND-ed.
SmartClient,WebClient,NGClient
var query = foundset.getQuery()
query.where.add(query.columns.flag.eq(1))
Methods Details
clearHaving()
Clear the having-part of the query.
SmartClient,WebClient,NGClient
var q = foundset.getQuery()
q.where.add(q.columns.x.eq(100))
query.groupBy.clear.root.clearHaving()
foundset.loadRecords(q);
exists(query)
Get an exists-condition from a subquery
Object
query
the sub query
SmartClient,WebClient,NGClient
foundset.query.where.add(query.exists(query2))
getColumn(name)
Get a column from the table.
String
name
the name of column to get
SmartClient,WebClient,NGClient
foundset.getQuery().getColumn('orderid')
getColumn(columnTableAlias, name)
Get a column from the table with given alias.
The alias may be of the main table or any level deep joined table.
String
columnTableAlias
the alias for the table
String
name
the name of column to get
SmartClient,WebClient,NGClient
foundset.getQuery().getColumn('orderid', 'opk')
getDataSource()
Returns the datasource for this.
String
SmartClient,WebClient,NGClient
getParameter(name)
Get or create a parameter for the query, this used to parameterize queries.
String
name
the name of the parameter
SmartClient,WebClient,NGClient
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.contact_id.eq(query.getParameter('mycontactid')))
// load orders where contact_id = 100
query.params['mycontactid'] = 100
foundset.loadRecords(query)
// load orders where contact_id = 200
query.params['mycontactid'] = 200
foundset.loadRecords(query)
getTableAlias()
Returns the table alias for this.
String
SmartClient,WebClient,NGClient
inline(number)
Create an inlined value. An inlined value is a value that will appear literally in the resulting sql.
For example
<pre>
query.where.add(query.columns.custid.eq(query.inline(200)))
</pre>
results in sql
<pre>
where custid = 200
</pre>
And
<pre>
query.where.add(query.columns.custid.eq(200))
</pre>
results in sql
<pre>
where custid = ?
</pre> with prepared statement value 200.
<p>
Inlined values can be used in situations where prepared statement expressions give sql problems, for example in some group-by clauses.
<p>
Note that using the same query with different inlined values effectively disables prepared statement caching for the query and may have a negative performance impact.
<p>
In case of a string will the value be validated, values that contain a single quote will not be inlined.
Number
number
value to inline
SmartClient,WebClient,NGClient
var query = datasources.db.example_data.order_details.createSelect();
var mult = query.columns.unitprice.multiply(query.inline(100, query.columns.unitprice));
query.result.add(mult);
query.result.add(query.columns.discount.max);
query.groupBy.add(mult);
inline(number, columnForType)
Create an inlined value converted to the type of the column.
Number
number
value to inline
QBColumn
columnForType
convert value to type of the column
SmartClient,WebClient,NGClient
var query = datasources.db.example_data.order_details.createSelect();
var mult = query.columns.unitprice.multiply(query.inline(100, query.columns.unitprice));
query.result.add(mult);
query.result.add(query.columns.discount.max);
query.groupBy.add(mult);
inline(string)
Create an inlined (quoted) value.
String
string
value to inline
SmartClient,WebClient,NGClient
var query = datasources.db.example_data.order_details.createSelect();
var mult = query.columns.unitprice.multiply(query.inline(100, query.columns.unitprice));
query.result.add(mult);
query.result.add(query.columns.discount.max);
query.groupBy.add(mult);
not(cond)
Create an negated condition.
QBCondition
cond
the condition to negate
SmartClient,WebClient,NGClient
foundset.query.where.add(query.not(query.columns.flag.eq(1)))
not(cond)
Create an negated condition.
QBLogicalCondition
cond
the logical condition to negate
SmartClient,WebClient,NGClient
foundset.query.where.add(query.not(query.columns.flag.eq(1)))
Overview
Content Tools
Activity