How to deal with database pagination when a row in between can be deleted by user action?
hibernate, java, mysql, pagination
Solution
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).
Problem
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.