CODEWITHSUNDEEP
sqlpagination
Jorge Ferreira
Jorge Ferreira

Reputation: 97997

Keep pagination repeatable if change operations are performed

If one wants to paginate results from a data source that supports pagination we have to go to a process of:

  1. defining the page size - that is the number of results to show per page;
  2. fetch each page requested by the user using an offset = page number (0 based) * page size
  3. show the results of the fetched page.

All this is works just fine not considering the fact that an operation may affect the backend system that screws up the pagination taking place. I am talking about someone inserting data between page fetches or deleting data.

page_size = 10;
get page 0 -> results from 0 to 9;
user inserts a record that due to the query being executed goes to page 0 - the one just shown;
get page 1 -> results from 10 to 19 - the first results on the page is the result on the old page 0.

The described behavior can cause confusion to the viewer. Do you know any practical solution to workaround this problem.

Upvotes: 1

Views: 1340

Answers (4)

Marko Balažic
Marko Balažic

Reputation: 556

One option is to exclude new incoming data objects from the result. This could be done with the session start time. You could add this for example to your JWT and then have a similar behavior like Twitter (14 new Twitts).

Upvotes: 0

Seun Osewa
Seun Osewa

Reputation: 5033

As long as users understand that the underlying data is always changing, they won't be confused. So just do it the straightforward way.

You could cache the first few pages of the result and use that for subsequent views, but then the results will be out of sync with the database, which is even more confusing.

Upvotes: 0

Doug McClean
Doug McClean

Reputation: 14495

If the updates you are concerned with are primarily new insertions (for example, StackOverflow itself seems to suffer from this problem when paging through questions and new questions come in) one way to handle it is to capture a timestamp when you issue the first pagination query, and then limit the results of requests for subsequent pages to items which existed before that timestamp.

Upvotes: 0

Mitchel Sellers
Mitchel Sellers

Reputation: 63136

There are a few schools of thought o this.

  1. data gets updated let it be
  2. You could implement some sort of caching method that will hold the entire result set (This might not be an option if working with really large Datasets)
  3. You could do a comparison on each page operation and notify the user if the total record count changes

.

Upvotes: 2

Related Questions