Pagination with SQL queries

Hi all,

I am trying to use and implement the server side pagination option on Retool but having a few issues.

(Read and tried using the doc on Optimize queries with server-side pagination | Retool Docs)

  1. I don't see the method of table1.pagination as valid and it gives me errors.
  2. Per 1, I've defined a 'total row count' and pageSize as you can see on the print screens attached.
  3. Tried using these variables on my query but it ends with no records returned. BUT not a failed query it seems.

Hope you can help me solve this as pagination can really help me as I am querying thousands of records and using hard coded limit which sucks.

Screenshot 2023-12-26 at 12.55.31
Screenshot 2023-12-26 at 12.55.10

This kinda looks like legacy Table, table1.pagination is within the new implementation of Table.

I would suggest trying the new Table.

@matth Thank you, but I don't think I get it.
I understand the 'pagination' they suggest on the docs is legacy and as you can see on my screen shots I am using the new version of the table.
Do you think you understand why I cannot config the pagination correctly?

When I look at legacy table I seeing similar setting to your screenshot with "Advanced..." accessible by clicking on Interaction.
Screenshot 2023-12-27 at 9.30.32 AM

in new Table, pagination is an add-on and the settings look like
Screenshot 2023-12-27 at 9.38.37 AM

Are you on Cloud or Self Hosted?

I see your point about the legacy table, correct.

I am on cloud.

As far as the query goes, if you would like to use legacy table I believe the correct limit and offset properties are

...LIMIT {{ tableLegacy1.pageSize }} OFFSET {{ tableLegacy1.paginationOffset }}

and for new table

...LIMIT {{ table1.pagination.pageSize }} OFFSET {{ table1.pagination.offset}}

Thank you, the query now works but returns all 3.7K results in one huge scroll of all results on the table. I am trying to find on the community and table config how to actually use 'pages' in the pagination so it will return the pageSize limit on page 1 and allow to go next until the final 3.7K record

What do your properties look like for Appearance?

Screenshot 2023-12-28 at 9.46.27 AM

I got it, it was the 'scroll' vs 'pagination' config on the Appearance. Now I got 163 pages :slight_smile:
Thanks you @matth looks to work fine now