How to deal with database pagination when a row in between can be deleted by user action?

Question

I am stuck in a situation. I hope I get a solution here. I am using paging to show some data on user profile page.

Using a basic paging to do so:

public Page list(Query query, int pageNo, int perPage) {

    int totalResults = query.list().size();

    int firstResult = (pageNo - 1) * perPage;
    query.setFirstResult(firstResult);
    query.setMaxResults(perPage);
    List resultList = query.list();
    return new Page(resultList, perPage, pageNo, totalResults);
}

Page class constructor:

public Page(List resultList, int pageSize, int page, int totalResults)   {
    this.resultList = resultList;
    this.pageSize = pageSize;
    this.page = page;
    this.totalResults = totalResults;
    this.totalPages = (totalResults - 1) / pageSize + 1;
}

Now if the results where to displayed normally without any deletion in between this would work fine. But a user can remove items from his action list.

Lets have the situation. Say there were totalResults - 17, perPage - 6 and totalPages - 3.

Now in the first page he deletes 2 items which leaves to total of 15 results and the row id which was to be in 2nd page has shifted to 9 instead of 7 (as two items are no there and the total result is now 15).

I hope the situation is clear. Now this intermediate shifting is not required.

I was thinking of implementing it as remainingItems but again I am bit confused in that. Remaining item would always be dependent on the pageNo and perPage and independent of the totalResult (since total result gets decreased in between). I cannot use last n rows here also as the row count will again decrease shifting my desired result.

I also do not want to keep too many parameter in the method. For example - lets say I pass the toSatrtWith id in the parameter and fetch perPage number of rows from there I may get the desired thing. But I want a more elegant solution.

Answer 1

Short answer: Remember "where you left off" rather than computing the location.

Long answer: See my blog on why "Pagination via LIMIT and OFFSET is evil": http://mysql.rjweb.org/doc.php/pagination . (You are doing the PHP equivalent of using LIMIT and OFFSET, so the blog applies.)

The url for the [Next] would include the id of the last item on this page, then use WHERE id > $id when building the next page. (Or id of the first item off this page, then WHERE id >= $id. This slight difference has a minor effect when a row is inserted between the two.) Also have this in the SQL: ORDER BY id ASC LIMIT 10. It will fetch only 10 rows, regardless of which page. Without this trick, you are fetching all the rows for all the pages before the current one.

[Prev] page is similar, but with a flag saying to go backwards (ORDER BY id DESC LIMIT 10).

[First] is probably best done by not specifying id; then the code will say "Oh, I should build the first page."

[Last] could include a flag for Last or id=-1 or whatever -- then use something like ORDER BY id DESC LIMIT 10 in the SQL.

Without this technique, a row that is deleted or inserted before the current page causes the list to be shifted off by one item (forward or backward), hence, the user misses (bad?) a row or gets a row duplicated (just annoying).