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