topic Re: Rest API - Pagination Not Working in Integration, Extension & APIs

Rest API - Pagination Not Working

Kevin-Marlow — Mon, 02 Mar 2020 20:46:59 GMT

Hi,

I'm using the REST API connector and I'm successfully able to connect to the data source, however the JSON service will only return 100 items per page and I need to pull 1,000's of items. I have checked the Response Header and the next page is displayed as 'link' item. If I select 'Pagination', 'Next URL' and enter '_response_header/link' as the 'Next URL' path, check 'Look in header' and run the reload it still only loads the first 100 items and never goes to the second page. Am I misunderstanding the use of 'Next URL' or is this functionality not supported?

Thanks,

Kevin

Re: Rest API - Pagination Not Working

Levi_Turner — Tue, 03 Mar 2020 08:56:42 GMT

Let's take an example which is actually how Qlik Cloud Services (QCS) forms their responses.

Response:

{ "data": [ { "name": "AppName", "resourceAttributes": { "_resourcetype": "app", "createdDate": "2019-12-12T17:43:32.964Z", "description": "", "dynamicColor": "", "hasSectionAccess": false, "id": "2694b8ce-0711-4bb7-ab98-08ca66daa45c", "lastReloadTime": "2019-12-12T17:46:12.060Z", "modifiedDate": "2019-12-12T17:46:12.461Z", "name": "AppName", "originAppId": "", "owner": "DOMAIN\\UserId", "ownerId": "ownerID", "publishTime": "", "published": false, "spaceId": "", "thumbnail": "" } } ], "links": { "self": { "href": "https://fake-tenant.us.qlikcloud.com/api/v1/items" }, "next": { "href": "https://fake-tenant.us.qlikcloud.com/api/v1/items?next=FgAAAAdfaWQAXj28vOUOhQABwV2UAA" } } }

For the Qlik REST Connector, the config for pagination URL would be:

root/links/next/href

The root bit is needed as an artifact of the Qlik REST Connector. The remainder is a straight-forward path to the element in the response.

Hope that helps.

Re: Rest API - Pagination Not Working

Kevin-Marlow — Tue, 03 Mar 2020 17:00:19 GMT

Maybe it's a limitation of the API I'm using but we don't see at the root level. We are using the FreshService API: https://api.freshservice.com/v2/#pagination

Pagination

API responses that return a list of objects, such as View List of Tickets are paginated. To scroll through the pages, add the parameter page to the query string. The page number starts with 1.

https://domain.freshservice.com/api/v2/tickets?page=2

By default, the number of objects returned per page is 30. This can be adjusted by adding the per_page parameter to the query string. The maximum number of objects that can be retrieved per page is 100. Invalid values and values greater than 100 will result in an error.

https://domain.freshservice.com/api/v2/tickets?per_page=10

The per_page and page parameters can also be used together. The following example will retrieve the 11th to 20th tickets.

https://domain.freshservice.com/api/v2/tickets?per_page=10&page=2

The 'link' header in the response will hold URL of the next page, if it exists. If you have reached the last page of objects, then the link header will not be set.

Headers:
"link":< https://domain.freshservice.com/api/v2/tickets?filter=all_tickets&page=2>;rel="next"

The Qlik Connector is generating the following code:

RestConnectorMasterTable:
SQL SELECT
"Transfer-Encoding",
"Connection",
"status" AS "status_u0",
"x-freshservice-api-version",
"pragma",
"x-xss-protection",
"x-request-id",
"link",
"x-frame-options",
"x-content-type-options",
"x-envoy-upstream-service-time",
"x-fw-ratelimiting-managed",
"x-ratelimit-total",
"x-ratelimit-remaining",
"x-ratelimit-used-currentrequest",
"Cache-Control",
"Content-Type",
"Date",
"Expires",
"__KEY__response_header"
FROM JSON "_response_header" PK "__KEY__response_header";

[_response_header]:
LOAD [Transfer-Encoding],
[Connection],
[status_u0],
[x-freshservice-api-version],
[pragma],
[x-xss-protection],
[x-request-id],
[link],
[x-frame-options],
[x-content-type-options],
[x-envoy-upstream-service-time],
[x-fw-ratelimiting-managed],
[x-ratelimit-total],
[x-ratelimit-remaining],
[x-ratelimit-used-currentrequest],
[Cache-Control],
[Content-Type],
[Date],
[Expires],
[__KEY__response_header]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY__response_header]);

DROP TABLE RestConnectorMasterTable;

and I'm see the following response:

Date: Tue, 03 Mar 2020 16:35:38 GMT
Content-Type: application/json; charset=utf-8
Transfer-Encoding: chunked
Connection: keep-alive
status: 200 OK
cache-control: no-cache, no-store, max-age=0, must-revalidate
x-freshservice-api-version: latest=v2; requested=v2
pragma: no-cache
x-xss-protection: 1; mode=block
x-request-id: 4952f88f-9436-4254-8f99-efd13dcf84bc
link: <https://freshservice.com/api/v2/tickets?page=2>; rel="next"
x-frame-options: SAMEORIGIN
x-content-type-options: nosniff
expires: Wed, 13 Oct 2010 00:00:00 UTC
x-envoy-upstream-service-time: 45
x-fw-ratelimiting-managed: true
x-ratelimit-total: 5000
x-ratelimit-remaining: 4998
x-ratelimit-used-currentrequest: 1

Re: Rest API - Pagination Not Working

AHMB — Mon, 15 Feb 2021 17:14:07 GMT

were you able to resolve this issue?

I'm facing the same problem with Freshdesk REST API. the pagination using "Next URL" does not work.

Re: Rest API - Pagination Not Working

Kevin-M — Mon, 15 Feb 2021 18:40:33 GMT

Yes, but I stopped using Pagination and started using 'With Connection'

Let startAt = 1;
Set Page = 500;
Let ValidateCount= 0;
Let FirstRun = 1;
For startAt = 1 to $(Page) step 1

LIB CONNECT TO 'FreshService_Tickets';

RestConnectorMasterTable:
SQL SELECT
"fr_escalated",
"spam",
"email_config_id",
"group_id",
"priority",
"requester_id",
"responder_id",
"source",
"status",
"subject",
"department_id",
"id",
"type",
"due_by",
"fr_due_by",
"is_escalated",
"description",
"description_text",
"category",
"sub_category",
"item_category",
"created_at",
"updated_at",
"deleted",
"to_emails",
"__KEY_tickets",
(SELECT
"@Value",
"__FK_cc_emails"
FROM "cc_emails" FK "__FK_cc_emails" ArrayValueAlias "@Value"),
(SELECT
"@Value" AS "@Value_u0",
"__FK_fwd_emails"
FROM "fwd_emails" FK "__FK_fwd_emails" ArrayValueAlias "@Value_u0"),
(SELECT
"@Value" AS "@Value_u1",
"__FK_reply_cc_emails"
FROM "reply_cc_emails" FK "__FK_reply_cc_emails" ArrayValueAlias "@Value_u1"),
(SELECT
"@Value" AS "@Value_u2",
"__FK_to_emails"
FROM "to_emails" FK "__FK_to_emails" ArrayValueAlias "@Value_u2"),
(SELECT
"__FK_custom_fields"
FROM "custom_fields" FK "__FK_custom_fields")
FROM JSON (wrap off) "tickets" PK "__KEY_tickets"
WITH CONNECTION(Url "https://<company>.freshservice.com/api/v2/tickets?per_page=100&page=$(startAt)");

Let CurrentLoad = NoOfRows('RestConnectorMasterTable');
Let ValidateCount= If($(ValidateCount)=0,NoOfRows('RestConnectorMasterTable'),$(ValidateCount));
Let startAt = If($(FirstRun)=0,If($(ValidateCount)=$(CurrentLoad),$(Page),$(startAt)),$(startAt));
Let ValidateCount = NoOfRows('RestConnectorMasterTable');
Let FirstRun = 0;
NEXT startAt;

Re: Rest API - Pagination Not Working

gamaescalante — Fri, 17 Feb 2023 22:44:33 GMT

That worked for me testing out the Departments Get Call. Appreciate your contribution to the community, seeing the same 30 departments was racking my brain!

Re: Rest API - Pagination Not Working

JBR1993 — Mon, 24 Jul 2023 08:09:39 GMT

Thnx! I've had some difficulties with the paging REST API, but this solutions works perfect!

Re: Rest API - Pagination Not Working

jerry654 — Thu, 31 Aug 2023 10:25:56 GMT

1. Verify the Endpoint:

Ensure that you are making the API requests to the correct endpoint that supports pagination. The endpoint should be designed to handle pagination, typically using query parameters like page and per_page.

https://domain.freshservice.com/api/v2/tickets?page=2

https://domain.freshservice.com/api/v2/tickets?per_page=10

The per_page and page parameters can also be used together. The following example will retrieve the 11th to 20th tickets.

https://domain.freshservice.com/api/v2/tickets?per_page=10&page=2

https://dtchiropractic.com

The 'link' header in the response will hold URL of the next page, if it exists. If you have reached the last page of objects, then the link header will not be set.

Headers:
"link":< https://domain.freshservice.com/api/v2/tickets?filter=all_tickets&page=2>;rel="next"

2. Check Query Parameters:

Review the query parameters you're using for pagination. Common parameters include page, per_page, limit, and offset. Make sure they are correctly formatted and are being sent in the API request.

Re: Rest API - Pagination Not Working

JQlik5 — Fri, 01 Sep 2023 04:40:10 GMT

Hi,

Can share me sample code for more understanding ....Thanks for added comments on my query...