How do I add pagination on MongoDB / Postgres Queries

How do I do X?
1

Tables are generally required to display large data sets from Queries and APIs, yet browsers cannot load all the data present in our database. To paginate this data, we can:

  1. Enable the server-side pagination property in the table
  2. Call the API / Query onPageChange.
  3. Configure pagination in the API / Query

Pagination on MongoDB

When binding queries from MongoDB on a table or list widget, we can add pagination by configuring the limit property inside the query. We can explicitly define how many rows we want to show on a table or list. In the case of the table widget, it’ll be:

{{ Table1.pageSize }}

After displaying the data on the first page, we’ll need to add an offset to show items on the page2; for this, we’ll need to set the Skip property to the following:

{{ (Table1.pageNo - 1) * Table1.pageSize }}

For example, if we’re limiting pagination to show ten times, the Skip/Offset will be evaluated to zero on the first page. On the next page, the offset will be set to 10, so and so forth.

Lastly, we’ll also need to configure the Total Record Count to limit the pages until the data exists. It basically disables using the next page button on the last page. This property expects the length of the query, hence it should be set to the following:

{{ Query1.data.length }}

Table1 is the table widget’s name, and Query1 is the query to which we add pagination.

Pagination on Postgres

To achieve pagination on data from Postgres, we configure the limit and offset properties within the query itself. Following is what the query should look like:

select * from users limit {{ Table1.pageSize }} offset {{ (Table1.pageNo - 1) * Table1.pageSize }}

First, we limit data on queries with the PageSize property of the table; note that this can be increased by manually increasing the height of the table widget. Next, we calculate the offset using pageNo and pageSize properties.

Last, we also need to set the Total Record Count to limit the pages until the data exists. This property expects the length of the query hence it should be set to the following:

{{ Query1.data.length }}

View the Forkable Template for Pagination

The following question is also discussed on How Do I Do X on Appsmith Session on our community call. Watch it here: How Do I Do X? - YouTube