SQL Offset and OS pagination

Hi,

Currently, I have a table records with pagination that shows 10 items per page. The source of this table is an SQL query.

My goal is, each time the query is executed, fetch only the 10 records required. 

For example, the first time the query is executed, fetch the first ten, then if I click on the 2nd page it will fetch the next ten and so on.

With the aggregates, I think this is made automatically, but with SQL I don't think so.

My approach was at the end of the query, add the following statement:

SyntaxEditor Code Snippet

ORDER BY @OrderBy
OFFSET @StartIndex ROWS
FETCH FIRST @MaxRecords ROWS ONLY;

where the input parameters have the following values:

@OrderBy - List_SortColumn_GetOrderBy(Table.Id,"Default Order")

@StartIndex - List_Navigation_GetStartIndex(Table.Id,True)

@MaxRecords - 10

Note that Table.Id and "Default Order" are for example purpose.

Then, when I try this approach, the first page is working with the first ten records, but when I try to access the following pages, no record is shown on the table. I already debug and the query is returning the right data but it's not appearing on the table records widget.

Any help with this?

Thanks



Hi Bruno,

What Outsystems does for aggregates is for the first page gets 11 records and for second page gets 21 records. If you are using the normal pagination try not using the offset to see if it works.

If it does maybe you need to create or own pagination widget.

Regards,

Marcelo

Solution

Hi Bruno,


using the RichWidgets\List_Navigation together with an aggregate or sql node, you can get an optimization by passing Max.Records as a parameter to the aggregate or sql.  

Notice the +1, this is suggested by OS in a warning, but I tried without and that also works, so not sure why this is.  Also good to remark, if you scaffold a list screen, Max.Records is empty for some reason, so that's not optimized at all.


When you want to add your own optimization in the actual sql with offset, you'll have to consider the following :

1° you'll need to separately retrieve the total count of rows fitting your filter criteria, since your sql no longer does this.  See SQLCountAllRows in image below, and this is info you need if you want to be able to navigate.  This is extra maintenance cost, since you'll have to make sure that filters between both queries stay in synch.

2° pass the startindex of the listnavigation in as offset

3° no longer need to set parameter Max.Records since you have it as a query parameter in the fetch clause


4° make sure you use the result of the SQLCountAllRows as TotalRowCount in both the navigation bar and the list counter widget.


5° you can put the Start Index of the table to 0, since sql takes care of the offsetting.


See attached oml for examples and comparison between different combinations of navigation and query/aggregate, and how the number of rows retrieved is influenced by these choices.

Hope this helps,

Dorine



Solution

Hi Bruno,


Dorine is correct. Because of your SQL, you'll get always 10 records.
If you set the start-index of you table to 0 you would see the returned records......

Hi,

I was able to fix it by setting the Start Index of the table records to 0. Now makes sense to me, because when I went to the 2nd page the start index was 10, but the SQL only returned records between the index 0 and 10. For that reason, the table was empty.

I will mark Dorine's answer as the solution, but to get the count of the total rows, instead of using a 2nd query only for counting, I add a COUNT (1) OVER() in the select statement of the main query and it's working fine. 

Thank you all