Table of Contents

Prepared Query Requests

To support the performant paging through big sets of the data using GET requests, the On Key API supports prepared queries. A prepared query is stored as an ordered set of record id's on the server side.

Important

Some important aspects to consider when using prepared queries:

  • Prepared Queries only work on GET requests that return a pageable collection of data for resources.
  • The batch associated with the preparedQueryBatchId is stored within the database and therefore consumes server side resources. These batches are cleaned up on regular intervals (every hour) to prevent bloat server side and will therefore not be available indefinitely.
  • The batch of records is created at the time that the first request is made. Paging through the batch in delayed fashion will not include any new or deleted records but will include the latest information for a specific record when the page containing the record is fetched.
  • Using an $orderby fragment on a prepared query request is not supported and will be ignored as the records are automatically ordered using the primary key of the table being paged.
  • The same $filter fragment, used to optionally scope the initial prepared query request, needs to be used across all prepared query requests. You cannot change the scope of data returned through using a different $filter or by adding a $filter to requests that use the same preparedQueryBatchId.

Query Parameters

The HTTP uri parameters used by prepared queries are:

Name Description Example
preparedQuery Flag to create a prepared query for paging through large sets of data ?preparedQuery=true
preparedQueryMaxBatchSize Maximum amount of records to include in the record set created for a prepared query ?preparedQueryMaxBatchSize=5000

To create a prepared query, append the preparedQuery=true parameter, and an optional preparedQueryMaxBatchSize=<limit> parameter, to a GET query string.

Tip

Always consider limiting the size of the record set for a prepared query by specifying a reasonable limit using the preparedQueryMaxBatchSize parameter and/or by adding a $filter segment to further narrow down the set of records.

On receiving the initial prepared query request, On Key will create an ordered set of at most <limit> records and store the id's of these records server side. The record set will be ordered by the primary key of the resource being queried. On Key will respond by fetching and returning the first page of records along with a preparedQueryBatchId=<id> and preparedQueryBatchSize=<available> in the initial response. The preparedQueryBatchSize will indicate how many records are actually available in the record set and it may be less than the requested preparedQueryMaxBatchSize.

Note

To limit the amount of records in a single response, add a $top=<xxx> paging fragment to your query. If the $top is omitted, On Key will return the default page size as configured for the system.

To page through the records, append the preparedQueryBatchId=<id> to the query string and use the the normal $top and $skip paging fragments to page through the data using subsequent GET requests.

Example

To illustrate, consider the following GET prepared query for paging through a maximum set of at most 5000 Work Order resource records. We limit the amount of records to return in a single response to 300 by adding a $top=300 fragment:

curl -v -X GET https://{server}/api/tenants/{client}/{connection}/modules/wm/workorders?preparedQuery=true&preparedQueryMaxBatchSize=5000&$top=300 \
-H 'Authorization: Bearer {accessToken}'

After submitting the initial prepared query request, On Key will return a preparedQueryBatchId and preparedQueryBatchSize along with the first 300 records in the response.

{
  "count": 300,
  "self": {
    "href": "api/tenants/{client}/{connection}/Modules/WM/WorkOrders/?preparedQuery=true&preparedQueryMaxBatchSize=5000&$top=300&$skip=0",
    "method": "GET",
    "type": "navigation"
  },
  "next": {
    "href": "api/tenants/{client}/{connection}/Modules/WM/WorkOrders/?preparedQuery=true&preparedQueryMaxBatchSize=5000&$top=300&$skip=300&preparedQueryBatchId=1624602735634918",
    "method": "GET",
    "type": "navigation"
  },
  "preparedQueryBatchId": 1624602735634918,
  "preparedQueryBatchSize": 5000,
  "items": [
    {
      ...
    }
  ]
}

The preparedQueryBatchSize of 5000 in the response indicates that we have 5000 records to page through. The next hypermedia link includes a link to fetch the next 300 records. Notice that the url includes the preparedQueryBatchId to indicate that we want to page through the same set of records that has been setup server side.

To fetch more records, we add the $top and $skip paging fragments along with the preparedQueryBatchId and preparedQuery=true to the query string of all subsequent GET requests:

curl -v -X GET https://{server}/api/tenants/{client}/{connection}/modules/wm/workorders?preparedQuery=true&$top=300&$skip=300&preparedQueryBatchId=1624602735634918" \
-H 'Authorization: Bearer {accessToken}'