How to fetch 10 records per page on click of pagination from Outsystem Entity

How to fetch 10 records per page on click of pagination from Outsystem Entity

  

Hi,

If i want to display the records in table format with pagination from DB, obviously i should use either aggregation or SQL.

Since entity contains 10,00,000 records,if we use aggregation, it will fetch entirely 10,00,000 records from the entity at a time. It will cause the memory and page load issue. 

How i wanted to achieve is when a page loads , we have to fetch only 10 records per page from DB that means initially table should display 10 records per page. When i click the number 2 in the pagination, another 10 more records should be fetched from entity. 

Hi Divya v,


Scaffolding is definitely the easiest way for you to do it. You just need to drag an entity to the web flow and the list screen with pagination will be automatically created for you (see here).


To limit the number of records fetched you can add to the max records of the aggregate / SQL query the following:

List_Navigation_GetStartIndex(<TableWidget>.Id,True) + <TableWidget>.LineCount + 1

This will limit the number of records fetched according to the page you are.

Hi Divya,

"if we use aggregation, it will fetch entirely 10,00,000 records from the entity at a time" - this is a wrong assumption. The Platform only retrieves as much records as needed. When the Aggregate output is bound to a Table Records or List Records, it will only fetch the number of records specified in its Line Count Property. When you loop over the results of an Aggregate, a database cursor is used, and the records are fetched one by one. Only when you pass the output of the Aggregate to an Action, all records are fetched (as they must all be available inside the action).

What you want, using pagination and only fetching the records needed, is as far as I know not possible. To use pagination, you can follow João's suggesting and look at how the automatic scaffolding handles it. This does mean however, that for every next page, all data upto and including that page is fetched, so it will get progressively slower when navigating (although some caching might occur). But in practice this is not a big problem, if you provide the right filters for the user to select the data they want.

some additional notes regarding aggregates

The aggregate will need to fetch all the records up to the records that are actually displayed on the screen. If you have a list count of 10 and the user is on page 1,000 the aggregate must fetch 10 * 1,000 = 10,000 record to display only the last 10. So the performance is decreasing when page numbers are increasing. You can use some advanced SQL technics here to avoid this problem but that’s out of scope of this article.

source: https://itnext.io/outsystems-how-the-pagination-navigation-widget-works-822526b06918

You can get the idea of how to implement the advance SQL using this article.

@John: That's what I already said ;)

Kilian Hekhuis wrote:

@John: That's what I already said ;)

Yes but I want to highlight that there's a performance problem still specifically on this :) - If you have a list count of 10 and the user is on page 1,000 the aggregate must fetch 10 * 1,000 = 10,000 record to display only the last 10


@John: Please read. I said "This does mean however, that for every next page, all data upto and including that page is fetched, so it will get progressively slower when navigating".

Hi divya v,

Please go through the below link it might help you

https://www.outsystems.com/forums/discussion/40925/fetching-part-of-data-from-database/#Post147426


Thanks

Rajendra Singh