Querying

RHTableQuery

RHTableQuery is an abstraction to filter, sort, and page a table query without having to write any SQL. Consider the following for querying the WebNodes view:

Frame nodes = $RHCore.RHTableQuery.New(prgCtx, 'WebNodes')

At this point no database query has been executed and the nodes object is a representation of all records in the WebNodes view.

The items method can be called to fetch the records. It constructs the query, executes it, and returns the results.

RecArray recs = nodes.items()

The underlying query is generated by the sql method, which can be called to inspect what's being executed:

echo( nodes.sql() )
> select WebNodes.* from WebNodes

Filtering

Filters are applied with the filter method and are used to reduce the result set based on a condition. The syntax is as follows (using nodes from our previous example):

nodes.filter(<columnName>, <operator>, <value>)

The parameters are:

• columnName - the column name on which to apply the filter;
• operator - the operator to apply (e.g., ==, !=, >, startsWith, contains, in, etc.); and
• value - the value to query.

The filter method changes the state of the object to include the condition in the query. For example, the following could be used to limit the nodes query to documents:

nodes.filter('subtype', '==', $TypeDocument)

A subsequent call to items would now only include documents.

Alternatively, we could use the in operator to limit the results to documents and folders:

nodes.filter('subtype', 'in', {$TypeFolder, $TypeDocument})

The filter method can be called multiple times to add additional conditions. For example, a second condition could be added to limit the folders and documents to names beginning with "HR":

nodes.filter('subtype', 'in', {$TypeFolder, $TypeDocument}) \
	.filter('name', 'startswith', 'HR')

Or, a third condition could be added to limit the results to items modified within the last 14 days:

nodes.filter('subtype', 'in', {$TypeFolder, $TypeDocument}) \
	.filter('name', 'startswith', 'HR') \
	.filter('modifydate', '=>', $RHCore.DateUtils.AddDays(Date.Now(), -14))

All filter operations are applied at the database level (i.e., in the "where" clause) for optimal performance.

Sorting

A sort criteria can be applied with the sort method and is similar to applying a filter. For example, the following sorts the nodes query by the name field:

nodes.sort('name')

The field name can also be prefixed with a negative sign to sort in reverse order:

nodes.sort('-name')

The method also permits sorting over multiple fields by passing in a list:

nodes.sort({'subtype','-name'})

As with filtering, sort is applied at the database level for optimal performance.

Paging

Results can be paged with the setPageSize and setPageNumber methods. For example, to set the page size to 25 and to get the contents of the 5th page is:

nodes.setPageSize(25).setPageNumber(5)

The items method would now return the 25 items on the 5th page after all filter and sort conditions have been applied.

Putting it together

You might have noticed the filter, sort, and paging methods each return the query instance. This allows the methods to be chained together into a single expression:

RecArray recs = $RHCore.RHTableQuery.New(prgCtx, 'WebNodes') \
		.filter('subtype', 'in', {$TypeFolder, $TypeDocument}) \
		.filter('name', 'startswith', 'HR') \
		.filter('modifydate', '=>', $RHCore.DateUtils.AddDays(Date.Now(), -14)) \
		.sort('-name') \
		.setPageSize(25) \
		.setPageNumber(5) \
		.items()

Aggregate Functions

RHTableQuery supports the count, max, min, and sum aggregate functions. For example, to get the number of documents in the system is simply:

Integer documentCount = $RHCore.RHTableQuery.New(prgCgtx, 'WebNodes') \
		.filter('subtype', '==', $TypeDocument) \
		.count()

TIP

Aggregate functions do not take paging into account.

See the RHTableQuery documentation for more information.

RHNodeQuery

RHNodeQuery is a subclass of RHTableQuery and provides additional features when querying nodes. By default, RHNodeQuery will only return nodes on which the user has at least See and See Content permissions. Additionally, a filterAttribute method is available to query on category and attribute values. This method transparently handles the necessary table joins and can also be chained.

See the RHNodeQuery documentation for more information.

Other RHTableQuery Subclasses

Additional query classes exist for LiveReports, Workflows, and users. See RHLiveReport, RHWorkflowQuery, and RHKUAFQuery for more information.