Skip to main content
Version: 2.36.0

Using Server Side Pagination in Tables

In this guide, we will implement server-side pagination for large datasets in a table component to enhance application performance. This guide is applicable for databases like MySQL, PostgreSQL, MSSQL, MongoDB, etc., supporting limit and offset for chunked data retrieval.

Loading Data from PostgreSQL in Chunks​

To fetch data in chunks from a PostgreSQL table users, use limit and offset in the SQL query:

PostgreSQL query
SELECT *
FROM users
ORDER BY id
LIMIT 100 OFFSET {{(components.table1.pageIndex-1)*100}};

The query will fetch 100 rows at a time from the PostgreSQL users table, and the number of rows returned is determined by the current value of pageIndex(exposed variable) in the Table component.

The following is the breakdown of the above PostgreSQL query:

  • ORDER BY id: Orders the result set by the id column.

  • LIMIT 100: Limits rows returned to 100 per query.

  • OFFSET {{(components.table1.pageIndex-1)*100}}: Determines the starting row number based on the current page index for pagination.

To obtain the count of records in the users table, execute the following query:

SELECT COUNT(*)
FROM users;

Edit the Table Component​

Follow the steps below to edit the properties of the Table component:

  • Drag the table component to the canvas from the components library and set the value of the Data property to {{queries.<postgresquery>.data}} to populate the table with the relevant data.

  • Enable the Server-side pagination option.

  • Click on the Fx next to Enable previous page button and set the value as below. This condition disables the previous page button when the current page is page 1.

    {{components.table1.pageIndex >=2 ? true : false}}

  • Click on the Fx next to Enable next page button and set it's value as below. This condition disables the next page button when the current page is the last page.

    {{components.table1.pageIndex < queries.<countquery>.data[0].count/100 ? true : false}}

  • Set the value of the Total records server side property as below. This will set the total number of records in the Table component.

    {{queries.<countquery>.data[0].count}}
Table data

  • To add the loading indicator on the table component while executing the query, set the Loading state property as:

    {{queries.<postgresquery>.isLoading}}

  • Select the Page changed event and choose the Run Query action, after clicking the New event handler. Then, select the Query from the dropdown that fetches data from the PostgreSQL table.

Table data

Now, whenever the page is changed, the query will be executed, and the data will be fetched from the PostgreSQL table in chunks.

Table data