DatabaseQuery interface finds rows in a table in a relational style database.
See Database for how to use this interface.
First, set up the query with
where() functions and the like.
Execution is implicit when you use the
Array-like interface, such as the
length property or access an element with the
Interface (build query)
All query specification functions return the query object, making them suitable for chaining.
function where(fieldName, comparison, value)
Add a where clause to the query, as in SQL. This function can be called multiple times to add multiple clauses.
comparison can be one of
"LIKE". Note that fields taking non-primitive values, such as
ref, can only use
"!=" is an alias for
In addition, for
labelList fields, the
"PERMIT READ" comparison may be used with a User
value to select rows where the user is permitted to read objects labeled with the given label list.
To select objects based on whether they have
null values, use the
"<>" operators only.
The values for
"LIKE" comparisons use the normal SQL wildcard characters,
_ for any single character and
% for zero or more characters. An exception will be thrown if
value is the empty string or the first character is a wildcard.
where() allows the use of the implicit
You can query on values in linked tables. If you have a field of type
"link", you can specify fields in the linked table in the
fieldName argument using the notation
"linkedField.fieldInOtherTable". Note that the first half is the name of the linked field, which is not necessarily the same name as the database table.
For example, if you had a linked field called
"department", and the table included a
"roomNumber" field, to find all employees in room
42, you might use a where clause like
Using this notation will create an join in the underlying database query, and include all the values of the linked table for efficient access. In the above example, it would behave as if you had also called
include("department") on the query object.
function whereMemberOfGroup(fieldName, groupId)
For fields of
user type only. Call to select on users who are a member of a given group, specified by group ID. Use schema
This works recursively where groups are members of the specified group.
Make an OR sub-clause, which matches rows matching any of its sub-clauses.
fn argument is a function, that function will be called with the sub-clause as an argument, and the object will return itself for chaining. Otherwise, the sub-clause is returned from this function.
or(), but creates an AND sub-clause, which matches rows matching all its sub-clauses.
For fields of
link type only. Call this to specify that the row from the linked table is loaded to.
If you’re going to access a row from a linked table for every single row in the results, use this to avoid a database query when you access the property.
This is a ‘hint’ for efficiency and will speed things up when used correctly, but makes no change to the behaviour of the API.
function order(fieldName, descending)
Add an order clause to the query. You can call this multiple times to specify orderings over multiple fields, as in SQL.
order() allows the use of the implicit
Set the order to an order which is consistent across queries, or if other
order() clauses are specified, add a lowest priority ordering. Use when you don’t want a ‘random’ order, but don’t mind what the order is as long as it’s always the same.
It’s faster to not specify any order at all, if you don’t mind what the order is.
Limit the number of rows returned from the query.
Skip the specified number of rows. Use with
limit() for implementing paged results.
Interface (aggregate functions)
Return the number of rows the query would return if it was executed. This is equivalent to a
SELECT COUNT(*) ... SQL query.
This is similar to the
length property, but has very different performance characteristics. Accessing the
length property executes the query and loads all the results. Calling
count() executes a separate SQL query.
Only use the
count() function if you are not going to access all the rows which would be returned by this query, otherwise you are performing unnecessary work in the database.
function aggregate(functionName, fieldName, groupByFieldNames)
Return the result of an aggregate function.
functionName is one of
"VAR_SAMP", as implemented by PostgreSQL.
fieldName is one of the fields in the database table, to which the aggregate function will be applied.
"id" can be used, which is useful to perform the equivalent of
groupByFieldNames is optional. If present, then the values are grouped by those fields. If present it should be an array of field names, or else a single field name as a string.
Returns a single value if
groupByFieldNames is not specified, otherwise an
groups properties. The
groups property is an object with properties for each of the fields specified in
groupByFieldNames, and a property
value with the aggregated value for the group. If only one field name to group by was specified, there will also be a property
group which contains the value of the column being grouped by.
Using any of these properties and functions implicitly executes the query.
acts as Array
Array. Use the
 operator to read rows.
The number of results from the query.
Since accessing the
length property will execute the query and load all the results, if you only want to know how many rows would be returned, used the
The iterator function is called for each row in the result with arguments
row is a
index is the zero based index into this ‘
Delete all matching rows from the table.
This can be called after the query has been implicitly executed. For efficiency, if
deleteAll() is called without accessing any of the results, no data is loaded.