DatabaseQuery interface
A JavaScript object implementing the DatabaseQuery
interface finds rows in a table in a relational style database, and can update them in bulk.
See Database for how to use this interface.
Construction
Call select()
on a DatabaseTable
object.
Usage
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 []
operator.
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 "="
, "<"
, ">"
, "<="
, ">="
, "<>"
or "LIKE"
. Note that fields taking non-primitive values, such as link
, user
and ref
, can only use "="
or "<>"
comparisons. "!="
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 "="
or "<>"
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 "id"
field.
Implicit joins
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 where("department.roomNumber","=",42)
.
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 GROUP
constants.
This works recursively where groups are members of the specified group.
function or(fn)
Make an OR sub-clause, which matches rows matching any of its sub-clauses.
If the 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.
function and(fn)
Similar to or()
, but creates an AND sub-clause, which matches rows matching all its sub-clauses.
function include(fieldName)
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 "id"
field.
function stableOrder()
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.
If a query uses stableOrder() and order(), the stableOrder() method has no effect, regardless of the order in which the methods are called.
function limit(count)
Limit the number of rows returned from the query.
function offset(start)
Skip the specified number of rows. Use with limit()
for implementing paged results.
Interface (aggregate functions)
function count()
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 "AVG"
, "COUNT"
, "MAX"
, "MIN"
, "SUM"
, "STDDEV_POP"
, "STDDEV_SAMP"
, "VAR_POP"
, or "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 COUNT(*)
.
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 Array
of Objects
containing value
and 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.
Interface (results)
Using any of these properties and functions implicitly executes the query.
acts as Array
A DatabaseQuery
object acts as a read-only JavaScript Array
. Use the []
operator to read rows.
property length
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 count()
function.
function each(iterator)
The iterator function is called for each row in the result with arguments (row, index)
. row
is a DatabaseRow
object, index
is the zero based index into this ‘Array
’.
function deleteAll()
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.
Interface (update)
A DatabaseQuery
can be used to efficiently update all rows matching the query.
function update(values)
The fields in every row which match the query will be efficiently updated to the values
, which is a JavaScript Object
used as a dictionary of field name to field value.
Returns the number of rows which were updated by the command.
For example,
P.db.customers.select(). where("country","=","GB"). update({ lastChecked: new Date() });
will update the lastChecked
field to the current date and time in all rows where the country
is "GB"
.