Overview

There are two different ways to retrieve data from an RDBMS in an expression, a!queryEntity() and a!queryRecordType. This page describes some of the properties of these queries and details the differences between them.

These queries can be called from any expression, so they can be reused across interface expressions, process models, and record type definitions. Parameters can be used to filter or page the array of data returned.

Comparisons

An a!queryEntity() is an expression function that takes a Data Store Entity and a Query as parameters and executes the query against that Entity. You can easily create simple entity queries using the Query Editor.

An a!queryRecordType() is an expression function that takes a process-backed or entity-backed Record Type and executes the query against that Record Type.

Operators

See the following table for information on the filter operators that can be used with a!queryEntity() and a!queryRecordType().

Paging Parameter

You can limit the number of results returned by your query by passing an optional paging parameter of type PagingInfo. This allows you to specify how many items should be returned and the sort of those items.

When a paging parameter is specified, a value of type DataSubset is returned. Use the a!pagingInfo() function to construct the paging parameter.

Applying Multiple Sorts

The following rules apply when applying multiple sorts:

• Fields will be sorted in the order in which the sort is provided. For example, if sorting on column A and B, the entity will be sorted by A, then B.
• When using selection, you can sort on any field on the entity, including those fields that are not part of the selection.
• When using aggregation, you can only sort on fields that are selected. Otherwise, an error will be returned.

Other

The queries you define are limited in how long they wait to return results (10 seconds). This setting can be configured by a system administrator.

There is also a limit to the amount of memory that can be returned by a single query. This is set by the conf.data.query.memory.limit value. The system will display an error with code APNX-1-4164-024 if this error is reached by a query. Use the paging parameter to return less data (or return data in batches) to avoid the limit.

See also: Configuring Query Limits

Best Practices

It is important that you tailor your queries to provide only desired information, especially when substantial growth in the data-sets queried is expected.

When designing queries, keep the following considerations in mind:

Constrain Your Results

By default, queries do not return a constrained subset of matching data records, unless you configure query conditions and filters and call the query using the PagingInfo parameter.

• In a typical SELECT query against an RDBMS, a constrained query might be built using the LIMIT clause. In Appian, this is achieved by calling the query with a PagingInfo parameter.
• Using a query, all records from an entity that match the query conditions are returned.
• The likely size of the data-set returned needs to be considered when building queries.
• A larger number of records returned may affect performance of the application.
• Instead of writing a rule that returns all records or many records, add query conditions and filters that limit the number of results to only those needed.

Use Additional Criteria

• Instead of selecting all records less than or greater than a given record, look for additional attributes that you can use to filter the results.

Use Multiple Conditions

• Use more than one query condition to reduce the number of records in the result set.

Problematic Design Example

The following improper design example illustrates how not to effectively implement queries.

Given a form that also displays the value of a previous item using a query, the following configuration might be used:

• A query named GetPrior(currentId) might be used to identify the next numerically-lower item according to the ID value.
• In this example, currentId is a rule input that holds the id value of the current item.
• If the query used a query condition of id < currentId, this query would effectively return all matching id values that are numerically lower than the currentId.
• In this example, only the highest-valued record out of the query's result set is desired.
• This approach returns much more data than is needed, resulting in a much larger system load than is necessary.

Best Practice Example

It is possible to implement a query with an additional rule input to function as a boundary for the attribute that you're filtering on.

For example, instead of id < currentId, you could implement two query conditions:

Id < currentId

— and —

Id > minimumBoundary

The query would then use two rule inputs GetPrior(currentId, minimumBoundary). This allows you to nest the query in one or more if statements within your expression.

For example:

1
if(length(GetPrior(currentId, currentId-1)) > 0, GetPrior(currentId, currentId-1), GetPrior(currentId, currentId-10))