Returning all results for a cursor-based paginated API

[antonybello]

We've had a lot of customers ask how to return all the responses for a cursor-based paginated API, where:

1. You make a request to an API
2. API returns the first n results and a cursor
3. cursor is used in a subsequent request to fetch the next n results

If you want to fetch all the results, create two queries. The first will be your API Query to fetch your results. The second is is a JavaScript Query, which calls the API Query as many times as we need to. In this case, we're using offset as our cursor.

Note that offset is undefined-- that's okay. We'll be using the JS Query to inject the value through additionalScope (more info here).

Now, we want to have a JS Query, like so: Recursive Fetch - Replit

// Airtable uses cursor-based pagination. This function recursively calls the API as long as the cursor from the previous request exists. 
const fetchAll = (offset, records) => {

  // Base case: we've reached the end, and there are no more cursors.
  if (offset == null) return records
  
  // Wrap the query result in a promise
  return new Promise(resolve => {
    return apiQuery.trigger({
    	additionalScope: {
      	offset
    	},
    	onSuccess: queryResult => {
        // Add the records from this query to all the records we have so far
        const newResults = records.concat(queryResult.records)
        
        return resolve(fetchAll(queryResult.offset, newResults))
    	}
  	})
  })
}

return fetchAll(0, [])

This query recursively calls the API Query as many times as we have offset returned to us (i.e., until we've returned all the data.)

[RadianWear]

I can't quite seem to get this to work, it just seems to loop infinitely?

[timAngus]

Remember to run the JS query not the API query, map through the data in the transformer and output the result of your transformer into the table.

[jordanp]

@timAngus could you provide an example here? i having a similar issue where my JS query seems to run infinitely. thank you!

[vangelov]

I did some tests with retrieving all data from a SQL query that's paged.

Here's a demo:

Cursor.json

Run the getAllUsers query

[RadianWear]

If I change the = null to ="" then it only runs once, but it is not running again when there are more records. I wonder if it is because I have a SELF and NEXT attribute and this query sees the null in the SELF? Do I need to check for a NEXT URL somehow?

{
"links": {
"self": "https://api.planningcenteronline.com/people/v2/people/#######/workflow_cards?filter=assigned&offset=null&where[stage]=ready",
"next": "https://api.planningcenteronline.com/people/v2/people/#######/workflow_cards?filter=assigned&offset=25&where[stage]=ready"
},

[nroeder]

I cannot seem to get this to work. My api result for a single call is

{
  "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users",
  "@odata.nextLink": "https://graph.microsoft.com/v1.0/users?$skiptoken=lkjalkdjfslkjd",
  "value": [
    {

[Kabirdas]

Hey @nroeder!

Since the cursor is passed as part of the URL you may need to configure your query slightly differently. There's a thread here that covers a similar sort of pagination, I'm curious if that's helpful for you at all. Otherwise, would you mind sharing screenshots of your current configuration (both the JavaScript and the query itself, along with the response your getting)?

[Shehryar_Amin]

Hey @antonybello ! Since the offset would be empty for the first request, how you happened to omit it in the first request? I am working on something similar but in Stripe so I have to remove the starting_after parameter for Stripe in the first request. Can you help me in that?

Cheers.

[Kabirdas]

Looks like this was answered here! (Just want to link in case anyone stumbles across this with the same question)

[Devrim_Celikay]

I've tried this, but it keeps returning the same first 100 queries. Then, it eventually stops and just leaves my table blank.

[Tess]

Hi @Devrim_Celikay,

That's a bit strange this code can be pretty specific to the api, so it may need to be modified a bit.

I'd try console logging queryResult.offset & newResults to see if you can find why/where it's breaking. Happy to look over more screenshots once you've implemented the console logs

[jamesbolt]

Hello Retool Team,

Thank You so much for sharing this helpful information, I applied this it is really working.

(James)