How to avoid showing results of page 1 in page 2?

Question

Here's the scenario:

1. I'm showing to the user (let's call him UserA) 10 items per page, ordering them by date (newest first).

2. As UserA still viewing page 1, another user (let's call him UserB) posts a new item.

3. Then, UserA decided to click on page 2. Now UserA is surprised to see the last item that appeared in page 1 is showing again as the first item in page 2.

How to solve this problem? (I think there are different approaches to solve this problem).

To clarify, this is my query:

select * from items order by publish_date desc limit <(PAGE_NUMBER - 1) * 10>, 10

To Note: "Infinite-scrolling" is not a solution, because behind the scenes infinite-scrolling is simply pagination where next page is requested automatically.

Alternative Scenario:

1. I'm showing to the user (let's call him UserA) 10 items per page, ordering them by date (newest first).

2. As UserA still viewing page 1, another user (let's call him UserB) deletes an item (let's suppose the deleted item is the last item in page 1).

3. Then, UserA decided to click on page 2. Now UserA sees 10 items of page 2, but he doesn't know that the last item of page 1 is different now (because it was deleted, so the first item of page 2 is now showing as the last item in page 1). So UserA misses viewing 1 item.

Answer 1

There is another kind of pagination that is harder to implements but will do what you want, it usually fits for infinite scrolling and it's more performant.

Basically you use the data from the last row as a parameter of your query :

SELECT * FROM sales
WHERE sale_date < ? ORDER BY sale_date DESC FETCH FIRST 10 ROWS ONLY

Where ? will be the value of your last sale_date.

It's called Pagination using seek method.

More information about it here : http://use-the-index-luke.com/sql/partial-results/fetch-next-page

However this cannot be implemented as freely than the limit method as you need to ensure to modify the parameters of your query according to the current filters and ordering of the users. If your page is a simple search page with only a very few filters or none, this will be doable.

If you have a complex search logic, keep the limit and as said Robert Harvey, it's by design. What you can do is to let the user have page or more than 10 rows on each page.

Answer 2

Fundamentally, your query needs to consider which items have already been seen. Since the position of items in your sort order is not stable (new items can be inserted at the beginning), you need to use a different way to identify the bounds of each "page".

Luckily, the relative position of items in your sort order seems to be stable (if an item was previously before another item, they will stay that way). Therefore, it should be enough to send in the last item of the current page, and request the next X items after it.

This will likely reduce performance, so do some benchmarking and ask yourself if the problem really matters, or if users can be expected to deal with it (we deal with similar experiences on other sites).

As you mention, you could also run into issues if items are ever removed (since you may end up looking for an item which no longer exists), so if you have that requirement I recommend simply marking them as deleted and removing all bits of information except for ID and publication date, and then skipping them in your query.

Answer 3

Reddit had to solve this too, because content is added rapidly. If you look at their pagination, you'll find URLs like these:

?count=26&before=t3_5jfkqr  (link from page 2 to page 1)
?count=50&after=t3_5j99dj   (link from page 2 to page 3)

Where 5jfkqr and 5j99dj are the first and last items of the current page, respectively and count the current position of that very item.

Using the before and after parameters the items can be fetched like this:

select * from items where publish_date < (
  select publish_date from items where id=<AFTER>
) order by publish_date desc limit <PAGE_SIZE>

select * from items where publish_date > (
  select publish_date from items where id=<BEFORE>
) order by publish_date asc limit <PAGE_SIZE>

(the results of the second query come in reverse order)

Page numbers

The count parameter is used to keep track of the page number.

Now, reddit only shows "prev" and "next" links, but this could be extended with a skip parameter. For example to link to the second next page:

?count=50&after=t3_5j99dj&skip=25   (link from page 2 to page 4)

This could be turned into a query like:

select * from items where publish_date < (
  select publish_date from items where id=<AFTER>
) order by publish_date desc limit <SKIP>, <PAGE_SIZE>

Limitations

If the first or last item of the current page gets deleted, pagination will break. So you should make sure that deleted items stay in the database with a deleted flag, instead of removing them entirely, at least for a grace period.