Skip to content

Paging API

Paging API using in-memory paging

Overview

Allows you specify a page size using in-memory paging which in turn enables you to iterate through large sums of data. Use the key from the POST request to use in the subsequent GET request. The GET request will use the same field selection, filters and sort order defined in the original POST request.

See also advantages and limitations of Keyset paging over In-memory paging.

Entry point

POST /api/tableaccess/job/paging/paged?pageSize=5

Authorisation

Use the bearer token obtained from GetBearerToken for authorisation.

Path parameters

pageSize=5

Resource URL

POST https://{environment}.retaincloud.com/DataAccessAPI/{tenant}/api/tableaccess/job/paging/paged?pageSize=5

Request body

Body:
{
    "fields": [
        {
            "fieldName": "job_description"
        },
        {
            "fieldName": "job_jobstatus_guid.jobstatus_description"
        }
    ],
    "order": {
        "orderFields": [
            {
                "field": "job_description",
                "order": "Ascending"
            }
        ]
    },
    "filter": {
        "filterGroupOperator": "And",
        "filterLines": []
    }
}

Response

{
    "key": "8c4cca1c-6ad0-4067-8719-bfe8305cfbe4",
    "rowCount": 50,
    "data": [
        {
            "job_description": "Job 1",
            "job_jobstatus_guid.jobstatus_description": "Unconfirmed"
        },
        {
            "job_description": "Job 2",
            "job_jobstatus_guid.jobstatus_description": "Planned"
        },
        {
            "job_description": "Job 3",
            "job_jobstatus_guid.jobstatus_description": "Planned"
        },
        {
            "job_description": "Job 4",
            "job_jobstatus_guid.jobstatus_description": "Planned"
        },
        {
            "job_description": "Job 5",
            "job_jobstatus_guid.jobstatus_description": "Unconfirmed"
        }
    ]
}

Subsequent Get requests

Use the Key returned in the response in subsequent GET requests. The page size returned in the POST request is 5. The total number of records returned in Response is 50. To export records 6 through 10, see example GET request:

GET https://{environment}.retaincloud.com/DataAccessAPI/{tenant}/api/tableaccess/job/paging/paged?id=8c4cca1c6ad040678719bfe8305cfbe4&from=6&count=5

Path parameters

  • id: 8c4cca1c6ad040678719bfe8305cfbe4
  • from: 6
  • count: 5

Use the above GET request over and over again by increasing the 'from' query string parameter until you have returned all the required records. The last request would be when the 'from' and 'count' parameters reach 46 and 5 to return records 46 through to 50 (which was the value returned in the rowCount from the initial POST).

To refer to the same paging ID before it expires, use Keep Alive message as shown below. Do this before/after each GET request.

POST https://{environment}.retaincloud.com/DataAccessAPI/{tenant}/api/tableaccess/job/paging/keepalive?id=8c4cca1c-6ad0-4067-8719-bfe8305cfbe4

Path parameters

id: 8c4cca1c6ad040678719bfe8305cfbe4

Paging API using (Keyset) Sequential paging

Overview

Allows you specify a page size which in turn enables you to iterate through large sums of data. Use the key from the POST request to use in the GET request. GET request will use the same field selection, filters and sort order defined in the original POST request.

Entry point

POST /api/tableaccess/job/paging/paged?pageSize=5&sequential=true

Authorisation

Use the bearer token obtained from GetBearerToken for authorisation.

Path parameters

pageSize=5

sequential=true

Resource URL

POST https://{environment}.retaincloud.com/DataAccessAPI/{tenant}/api/tableaccess/job/paging/paged?pageSize=5&sequential=true

Request body

Body:
{
    "fields": [
        {
            "fieldName": "job_description"
        },
        {
            "fieldName": "job_jobstatus_guid.jobstatus_description"
        }
    ],
    "order": {
        "orderFields": [
            {
                "field": "job_description",
                "order": "Ascending"
            }
        ]
    },
    "filter": {
        "filterGroupOperator": "And",
        "filterLines": []
    }
}

Response

{
    "key": "8c4cca1c-6ad0-4067-8719-bfe8305cfbe4",
    "rowCount": 50,
    "data": [
        {
            "job_description": "Job 1",
            "job_jobstatus_guid.jobstatus_description": "Unconfirmed"
        },
        {
            "job_description": "Job 2",
            "job_jobstatus_guid.jobstatus_description": "Planned"
        },
        {
            "job_description": "Job 3",
            "job_jobstatus_guid.jobstatus_description": "Planned"
        },
        {
            "job_description": "Job 4",
            "job_jobstatus_guid.jobstatus_description": "Planned"
        },
        {
            "job_description": "Job 5",
            "job_jobstatus_guid.jobstatus_description": "Unconfirmed"
        }
    ],
"rowsProcessed": 5
}

Use the Key returned in the response in subsequent GET requests. The page size returned in the POST request is 5. The total number of records returned in Response is 50. To export records 6 through 10, see example GET request:

GET https://{environment}.retaincloud.com/DataAccessAPI/{tenant}/api/tableaccess/job/paging/sequentialpaged?id=8c4cca1c6ad040678719bfe8305cfbe4&count=5

Path parameters

  • id: 8c4cca1c6ad040678719bfe8305cfbe4
  • count: 5

Start after the last record processed in the previous call and continue from the next available record in the next call.

Note

If rowsProcessed is different than the pageSize requested in the api call, then the number of rows processed can break the pageSize, if the total number of rows are less than the required pagesize or if the last database row returned in the dataset has nulls in any keyset filter field. In this case, the value cannot be used as the starting value for the next filter criteria, so the value from the row above is taken instead, and the rowsProcessed is recalculated accordingly.

rowsProcessed can be different than the pageSize requested in the api call in the following scenarios:

  • If the total number of rows are less than the required pagesize or
  • If the last database row returned in the dataset has nulls in any keyset filter field. In this case, the value cannot be used as the starting value for the next filter criteria, so the value from the row above is taken instead, and the rowsProcessed is recalculated accordingly

Advantages and limitations of Keyset (Sequential) paging

Advantages

The key advantage of using Keyset (Sequential) pagination over the in-memory pagination is the minimum use of the service fabric memory. In keyset pagination, only basic information is loaded in the memory store. Data from the database is loaded directly using the last loaded reference point to the data. This gives an improved performance and fast access to data for each request without compromising the amount of in-memory storage.

Limitations

  • The Filter fields should be similar to the order fields when creating the payload
  • All order by fields must be part of the selection list
  • Sub filters are not supported for now
  • Table_guid cannot be used as one of the filter criteria in the payload because it has been used internally for maintaining the uniqueness in keyset filter
  • Any greaterthan or greaterthanequals operator requires to have the 'order by' clause with ascending order, and any lessthan or lessthanequals operator requires to have the 'order by' clause with descending order
  • The 'In' operator is not supported in this release