Configuring server-side pagination
Last updated
Was this helpful?
Last updated
Was this helpful?
If your dataset contains thousands of records, you may struggle with load time and app performance. To improve performance, you can configure server-side pagination allowing you to load only the records on the current page.
Here, we talk about the component. But the guide is also suitable for and components.
It's important to keep in mind that when server-side pagination is enabled, table inline filters and sorting don't work automatically and need to be .
In this article, we'll explore two types of server-side pagination:
To implement page-based server-side pagination, follow the steps below:
For the Table component, select the Server side pagination checkbox in the right side panel.
Next, create a new action of the SQL Query type and use the {{ui.table.pageSize}}
and {{ui.table.paginationOffset}}
variables to control the data you need to load:
Now, assign this action to the On Page Change trigger of the Table to ensure the data is reloaded with each table page navigation.
Finally, set the Table's Show loading setting to true while your SQL Query action is loading - add the {{actions.loadData.loading}}
variable.
Done! Now the Table will display only the records for a specific page.
Let's review cursor-based pagination based on the Stripe API example. Say you want to select a list of customers from Stripe API. To do that, follow the instruction below:
For the Table component, select the Server side pagination checkbox in the right side panel.
Next, create a new action of the HTTP Request type and specify the {{ui.table.pageSize}}
and {{ui.table.afterCursor}}
variables in JS mode - Query Params.
Now, in Table settings, set the following:
{{_.last(actions.customers.data.data).id}}
to the Next cursor field (the identifier used for the next set of results),
{{actions.customers.data.has_more}}
to the Has next page field to enable or disable the next page button according to API info,
where actions.customers is the action we created in Step 2.
Assign your HTTP Request action to the On Page Change trigger of the table to ensure the data is reloaded with each table page navigation.
By default, the Table doesn't know the total number of items and can't disable the Next page button if the limit is reached. To display the total item count and make the table more intuitive, you can set the Total row count setting. Based on it, the number of pages will be calculated and displayed according to the items per page.
To retrieve the total row count, you simply need to create an action that will retrieve the total number of items or get this info from the API you are using. For example, you can use an action of the SQL Query type:
When server-side pagination is enabled, table filters and sorting don't work automatically, as the Table cannot filter and sort the data while it's being loaded page by page.
To configure server-side filters, you can use the {{ui.table.filters}}
variable in your action to send this data to your API/SQL Query or Load Table action.
When using filters, make sure the SQL query is configured in a way that it returns all records when the filters are empty, instead of trying to search for empty records. For instance, for most SQL databases you would need to use a LIKE operator combined with the % sign, which represents zero, one, or multiple characters:
And don't forget to assign the action with the filter variable to the On Filters Change trigger.
To enable sorting, use the {{ui.table.sortColumn}}
and {{ui.table.sortDirection}}
variables in your action.
After you disable the Prepared statement setting, your SQL Query will look like this:
You can set the default sort column and direction in JavaScript using the ??
operator. In our example here, it means - "if sortColumn
is empty, use id
column instead".
And don't forget to assign the action with the sorting variables to the On Sort Change trigger.
If everything is configured properly, but the action does not return the expected values, check the following:
By default, dynamic sorting doesn't work as the Convert SQL queries to prepared statements . You need to turn this setting OFF to allow dynamic sorting. But be cautious - disabling this setting can cause SQL injection!