Hi All!
First time user of Retool here. I've read some of the top posts about dealing with pagination, so my apologies if this was already covered and I missed it.
I'm working with an API where the page parameter is called in the Header
The API outputs the first 100 results
If the response has a Status == 206[Partial Return] then this means there are more than 100 results to that request.
My JS skills are close to 0. I would usually get this done in python by creating a while loop and counter based on the condition that if Status == 206 then page =+ 1
I'd love to use something like Retool for this and not have to right a custom python script, create a cron job, etc.
Any ideas how to write the JS query to get this done?
Much appreciated!
You might try using cursor based pagination using the page number as a kind of cursor. Setting that up in Retool requires passing a value that indicates to the table whether or not there's more data to load. So, in this case, you're "Next cursor" value would be page + 1 and your "Has next page" value would be Status == 206. Something like the following:
To verify that could work though, can you check the metadata property of your query to see if the status is included there?
Hi @Kabirdas appreciate the response!
Unfortunately, I do not see Status included in the metadata. At least, I think I'm not seeing it..
How did you get that output?
Forget what I just said. Yes, it does.
But my Next cursor query does not seem to work. When I type that in I get
Error
Cannot read properties of undefined (reading 'page')
Got it, there was some guesswork there on my part. Since all you need is the page number, you might actually try not using the Next cursor at all and just passing table.selectedPageIndex directly to your query. Does that work?
Going to need a bit more hand holding on this. Here is what my query looks like:
Where do I add that? In URL parameters?
Thanks!
No problem! Where you add it depends on what your API is expecting. You mentioned earlier that you pass the page as one of the headers, correct? Try setting the key to whatever your API expects and then passing {{ yourTableName.selectedPageIndex }} as the value. (You might need to pass {{ yourTableName.selectedPageIndex + 1 }} depending on if your API is 0-based or 1-based)
I'm reviving this post from the dead lol! I'm revisiting this and really want to leverage Retool's FE UI capability rather than maintaining my python scripts!
Ok, @Kabirdas to recap.
- The page parameter is passed in the header.
- In python, I'd have a condition where I'd loop through the pages until
{{ metadata.status === 206 }} is no longer true.
I've added the {{ metadata.status === 206 }} as one of my Failure Conditions in the Response section.
I still can't figure out how to run the "loop" in retool. My query is called "query1". I tried passing
{{query1.selectedPageIndex}} but I'm still only getting the first 100 lines
Hey @Dimitri_Lianoudakis!
Are you looking to loop through all of your pages and return them together or set up a server-side paginated table?
Seeing an example request and response from your API would also be helpful for getting the correct syntax!
Yes, I'd like to be able to loop through all pages and return all results. I don't know enough about server-side pagination to determine if that's what I need here...
Here is my API request
Here is the response
Here is where the status code shows up:
based on these docs it looks like you might need to add a page parameter to your headers.
Setting up server-side pagination on a table just means that you'll get the data from your server at the moment you switch to a new page on your table instead of getting all of the data beforehand. Depending on how many rows you have this can help quite a bit with performance, but whether or not it's the right solution also depends on what you want to do with the data in your app.
Getting all of the results together actually requires a bit more code than setting up server-side pagination for a table but it is doable! You'll want to create a separate JavaScript query - from there you can run a loop that queries each page of your data.
const maxPages = 100; //set this to whatever you what the upper limit of pages to be
const results = [];
for(let i = 1; i <= maxPages; i++){
//fetch the current page of data
const pageData = await query1.trigger({additionalScope: {page: i}});
//at the page's order data to your results
results.push(...pageData.orderData);
//check to see if the status is not 206 and if so, exit the loop
if(pageData.status !== 206) break;
}
return results;
In query1 itself, you'll want to pass {{ page }} to the page header. It will show a warning since it won't be defined until the moment you trigger the query using additionalScope as shown above. You can read a more general description of additionalScope here!
The collected results should then be available in the JavaScript query's data property.
Let me know if that works and/or raises any additional questions 
