Server-side pagination

How to set up server side pagination for a table

If your dataset contains thousands of records, you might struggle with load time and app performance. Server-side pagination allows you to load records that are on a current page only.

The following guide is suitable for the Table, Grid view and List view components.

When server-side pagination is enabled, table inline filters and sorting don't work automatically and need to be implemented separately.

Page-based pagination

To implement page-based server-side pagination, follow the below steps:

1. Enable the Server-side pagination table setting in the component's configuration sidebar.

2. In your load action use {{ui.table.pageSize}} and {{ui.table.paginationOffset}} variables to control the data you need to load. With these variables, you can configure your action to only load the page that the table requests, by sending pageSize and paginationOffset variables to your API or Load table action.

3. Assign this action to the On Page Change trigger of the table, to ensure the data is reloaded with each table page navigation

4. Set table Show loading setting to be true while load action is loading - {{actions.loadData.loading}}

Server side pagination example

Cursor-based pagination

1. Enable the Server-side pagination table setting in the component's configuration sidebar.

2. In your load action use {{ui.table.pageSize}} and {{ui.table.afterCursor}} variables to control the data you need to load. With these variables, you can configure your action to only load the page that the table requests, by sending pageSize and afterCursor variables to your API or Load table action.

3. Your action should return info about the next page cursor nextPage and if it has the next page hasNextPage (optional).

   1. Set {{actions.loadData.data.nextPage}} to the Next cursor field. This will provide the .afterCursor data for the next page in the table.

   2. Set {{actions.loadData.data.hasNextPage}} to the Has next page field to enable or disable the next page button according to API info.

4. Assign this action to the On Page Change trigger of the table, to ensure the data is reloaded with each table page navigation

5. Set table Show loading setting to be true while load action is loading - {{actions.loadData.loading}}

Total row count

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 specify the total row count. Based on this, the number of pages will be calculated and displayed according to the items per page.

To retrieve the total row count, you will need to create an additional action that will retrieve the total number of items or get this info from the API you are using. For example, a query:

SELECT COUNT(*) AS AMOUNT FROM orders;

When using a Load Table action type, you can obtain the row count using {{actions.loadData.res.total}}. In this case, you may not need the additional action to calculate the total number of records and can utilize this property to show the pager.

Filtering

When server-side pagination is enabled, table filters and sorting don't work automatically, as it cannot filter and sort the data while it's being loaded page by page.

To configure server-side filters and sorting, you can use {{ui.table.sortColumn}}, {{ui.table.sortDirection}} and {{ui.table.filters}} variables 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 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:

select
  *
from
  users
where
  users.name like CONCAT ('%', {{ ui.table.filters.name}}, '%')
limit
  {{ui.table.pageSize}}
offset
  {{ui.table.paginationOffset}}

Do you forget to assign the action to the On Filters Change trigger.

Sorting

To enable sorting, use {{ui.table.sortColumn}}, {{ui.table.sortDirection}} in your action.

By default, SQL databases are configured with the Convert SQL queries to prepared statements option enabled. This option prevents dynamic changes of sort and direction statements of a SQL query. Disable this setting to allow the usage of dynamic sorting.

Do you forget to assign the action to the On Sort Change trigger.

When the prepared statement setting is disabled, the query shall appear as follows:

select
  *
from
  users
where
  users.name like CONCAT ('%', '{{ ui.table.filters.name }}', '%')
order by
  {{ui.table.sortColumn ?? 'id'}} {{ui.table.sortDirection ?? 'asc'}}
limit
  {{ui.table.pageSize}}
offset
  {{ui.table.paginationOffset}}

We can set the default sort column and direction in JavaScript using the ?? operator. In this case, it means "if sortColumn is empty, use id column instead".

Notice how the name filter now requires quotes around the variable name:

CONCAT('%', '{{ ui.table.filters.name}}', '%')

This happens because the query is not converted to prepared statements. We need to add proper quotes around string values manually.

Troubleshooting

If everything is done properly, but the action does not return the expected values, check the following:

1. Check that the action is assigned to all required triggers - On Page Change, On Filters Change, On Sort Change.

2. Check that the action that handles filters and pagination is the same action that is connected to the Data property of the table.

3. If dynamic sorting is enabled - make sure the Convert SQL queries to prepared statements option is disabled and proper quotes are used around the variables.

4. Check the payload tab, copy the query and parameters, and manually run against the database to ensure it is properly constructed.

5. Ensure that when setting your filters, if a value is left blank or set to NULL, the query should retrieve all records based on that particular filter criterion, rather than just retrieving records with blank or NULL values for that criterion.

6. If you notice that some of the variables are sent as 'null' or 'undefined' string values, adjust your variables interpolation to account conversion of these values to empty strings or other values using the ?? operator CONCAT('%', '{{ ui.table.filters.name ?? '' }}', '%')