Pagination
Client-side
Currently, the table is displaying all the records. If you had a large database of players, this would make your app very slow to load and would put unnecessary stress on your database. Instead, we want to be able to limit the number of player records we display when the app is loaded. We then want to add some buttons which would allow you to navigate forwards and backwards through these pages of records.
We can achieve pagination by adding an add-on component.
The query is still retrieving all the records but they can now be paged through rather than scrolling.
This is suitable for small datasets but for cases of thousands of players, we only want to return a single page at a time.
Server-side
The specifics of how server-side pagination is implemented will depend on the nature of the data source on the server.
For our test app, we are using SQL queries on the Retool database so we use the limit-offset method.
Click on the pagination add-on that was just created and enable server-side pagination, selecting the limit-offset type.
This requires table properties to be used as parameters in the query, ‘pageSize’ and ‘offset’. Both of these are set automatically by the table, but pageSize can be hard-coded if necessary.
We will update the ‘get_players’ query to add these properties.
This pagination method also requires a ‘total row count’ property to be set so that the table can calculate the number of pages. We need to add a separate query to get this number when using SQL-based data resources, which may not be suitable for massive datasets such as logs or game transactions.
Create a new query ‘count_players’ and add the following SQL statement.
Then, reference this query in the ‘total row count’ field of the pagination add-on.
Trying to navigate to additional pages will change the page number but that date will currently not update the records.
This is because we need a final step to make the ‘get_players’ query ‘Run automatically when inputs change’. We will add this functionality next.
This will cause the query to execute when the ‘offset’ parameter is changed by navigating to higher pages.
Note
This means that searching for players by email or name will also cause the query to execute as they are inputs to the query.
We are now able to paginate through the players table, with the query returning a single page of player records at a time. If you want to add more records per page we can edit the table to extend it down and make the page size larger. Retool takes this into account and sets the LIMIT parameter so that the query does not need to be changed.
In our use case we are displaying players by most recent login, it may be sufficient to hardcode this value at say 100 or 1000, if the user will not need to see records beyond this in a real-world use-case.
If our case involved filtering by a date-range, then the total count of the filtered dataset would have to be determined on the server and returned along with the page of filtered data.
If our datasource is an API, then we can use URL parameters for page, limit and total and return both the data and required parameters in a single request.
If our datasource can support it, we can use cursor-based pagination to avoid having to calculate the total record count. This method returns a single page and a cursor to keep track of where it is in the dataset. By sending the cursor in subsequent requests along with page size, the datasource can return pages until the cursor is null, indicating that we are at the end of the dataset.
Retool also supports GraphQL Relay cursor-based pagination, but that is outside the scope of this tutorial.
https://docs.retool.com/data-sources/quickstarts/api/graphql
Summary
Now we have implemented server-side pagination to improve application performance and limit data transfer in our requests.
From here, we will handle JSON data so that it can be displayed and updated by the application.