How do I paginate data server-side?

[lanedsmu]

In Lowcoder, queries are returned to the browser as they're executed, regardless of how many rows are displayed in a table page (for instance). In order to improve performance, it is often necessary to paginate the SQL queries behind our applications. The exact syntax for doing this varies across DB platforms, and we'll hit two main syntax forms here.

We'll start with a table displaying the results of a simple query:

Here we have three columns and several rows. If this were a dataset of many thousands or millions of rows, we wouldn't want to have all of that be loaded to the browser. Instead, we need a way to query just enough to fit on the page. Enter select offset/limit/fetch.

SQL Query Pagination

Using SQL offset/fetch(limit) in combination with the Lowcoder table properties pageNo and pageSize, we can generate SQL that will automatically query only the rows we need for the display. In our example here, we'll use SQL that looks like one of these examples.

Below is SQL Server and Oracle syntax, using OFFSET/FETCH:

SELECT * from USPop
ORDER BY date
OFFSET {{(table1.pageNo - 1) * table1.pageSize}} ROWS
FETCH next {{table1.pageSize}} ROWS ONLY

MySQL/PostgreSQL would look similar, using LIMTI/OFFSET:

SELECT * FROM USPop
ORDER BY date 
LIMIT {{table1.pageSize}} OFFSET {{(table1.pageNo - 1) * table1.pageSize}}

Lowcoder table properties

In addition to the SQL changes, Lowcoder also needs to know how many rows there are, total, so as to know that there will be multiple pages of data. If we don't do this, the SQL query will only ever return the number of rows that will display in first page of the table, and there will be no way to paginate through them, since Lowcoder will recognize (correctly) that it has displayed all the data from the query.

To address this, Lowcoder tables have a "Total Row Count" property that we can populate using a count(*) SQL query:

In our case, our query looks like this:

select count(*) as TotalRows from USPop

pageSize and pageNo population

One problem we will run into with default settings on these queries is that the queries may be executed before the table is populated.
This means that our pageSize and pageNo values may be null.
When this happens, we get an error like this:

qryAllRowsPaginated run failed: {"code":"QUERY_EXECUTION_ERROR","message":"Query execution error: The number of rows provided for a OFFSET clause must be an integer.."}

This doesn't actually break the application, but it does present a message that we'd like to avoid.

There are a variety of ways of handling this, but one of the simplest is to use the JS /or/ construct in our SQL handlebars for null handling. Doing this, we can hard code offset and fetch values in our SQL for those times when the query is executed before the table is rendered.
That modified SQL looks like this, replacing null values with 0 and 5 as appropriate:

MSSQL/Oracle:

select  * from USPop
order by date
offset {{(table1.pageNo- 1)||0 * table1.pageSize||5}} rows
fetch next {{table1.pageSize||5}} rows only

MySQL/PostgreSQL

SELECT * FROM USPop
ORDER BY date 
LIMIT {{table1.pageSize||5}} OFFSET {{(table1.pageNo - 1)||0 * table1.pageSize||5}}

With these changes in place, we have a responsive app that will display large datasets more efficiently.

[yogeshvachhani]

I believe that in case of Table Component all the data should be fed to it and the table should automatically do necessary pagination without the developer having to do anything at all.

[FalkWolsky]

No. This we cannot say as "automatism". The usecases are truly very different.
Some users may bind data to APIs - and pagination would look truly different than in SQL approach - just as one example.