Query Collection Data with key base pagination

[bradhuang-evg]

@HenrikHL
In API design principle, it says "GET requests on collection results SHOULD implement pagination".
The code in github maybe should change into key base pagination, since must of our data might be huge.
Because I want to make sure about the detail of key base pagination.

Following the example from Oracle

EX:

If limit=10&cursor=OOO&sort=eventCreateDateTiime
And the SQL will be

SELECT *
  FROM OUR_TABLE
 WHERE KEY > :cursor
  ORDER BY KEY
 FETCH NEXT 10 ROWS ONLY

Question 1: How to get cursor for last-page? Is it the cursor for the last 10, or the last MOD(totalRecordNum, 10)?
Question 2: How to get cursor for first-page? I think query without cursor will become first-page automatically.
Question 3: How to handle sort and key base pagination together, since we already need to order by key to key base pagination

[HenrikHL]

Hi @bradhuang-evg,
It is correct that the current implementation for pagination in Core is Offset-based. This was to make it simple and quickly implement pagination. If this model is going to be used on huge amounts of data - then Yes - keybased pagination should replace this.

Question 1: The original idea was to make the last page display the last LIMIT number of items.
After talking internally about this - we are thinking about removing the last_page header. It is difficult to compute this page and will most likely not be used very often. We are also discussing removing the prev_page as this is also difficult to compute. Removing any of these would be a breaking change - so it will not be removed now.

Question 2: Cursor for first page is as you write - queryParameters without cursor :-)

Question 3: It is possible to handle sort and key based pagination at the same time. It is though not possible to change the sort order when displaying a next_page link. As soon as a specific page is accessed/displayed (using one or more next_page links) - changing the sort-order will reset the pagination to first page (with the new sort order...)
Edit: check a look at this link: https://medium.com/swlh/how-to-implement-cursor-pagination-like-a-pro-513140b65f32 for a suggestion as to how to implement pagination and sorting

[bradhuang-evg]

Thanks for your response.
Question 1: Maybe we should deprecated prev_page, last_page even first_page as well in TNT 1.2.1 and 2.1.
Because GET /events may return a huge amount of data.

Question 2: In API design, client can sort with multiple column at one time.
But it is to complicate or even impossible for key base pagination to sort with multiple column.
EX: sort=name,age:desc

[HenrikHL]

I agree that GET /events will return a huge amount of data.
We will change the reference implementation from the current offset based -> key based to test it.
I have created this issue #75 - we will start looking at it as soon as possible

[HenrikHL]

@bradhuang-evg - why do you want to remove first_page header as well? This is the simplest of them to implement...

[bradhuang-evg]

@HenrikHL
Of course it is easy to implement it.
I just think that first-page(no cursor) and current-page(current cursor) header are unnecessary for client.
Because they can get the link by themselves.