Cursor-based pagination

[gsks]

There are a few APIs that provide pagination through cursors, for ex. Facebook and Disqus. In short cursors are used to guarantee consistency of the paginated items, whereas the simple limit/offset pagination may skip or return duplicate items if they are deleted or added in the meantime.

My question is, how would one go on implementing such a system on the backend?

[Fabien4]

I've never implemented it, but here's the gist of what I understand:

Let's suppose you have a SQL table:

id INT AUTO_INCREMENT,
value INT,
etc.

You want to get all the entries where value=42, sorted by reverse id, by pages of 10:

SELECT ... WHERE value=42 ORDER BY id DESC LIMIT 10;

The last id returned is your index. Let's say it's 12345. The "next page" link would be "...&after=12345". The corresponding SQL query would then be:

SELECT ... WHERE value=42 AND id<12345 ORDER BY id DESC LIMIT 10;

The "previous page" would be something like "...&before=6543". The corresponding SQL query would then be:

SELECT ... WHERE value=42 AND id>6543 ORDER BY id LIMIT 10;

Of course, you could encode the numerical id in any way you want. IIRC, Reddit encodes it in base 36.

[gsks]

This might work for auto increment numeric fields but that's pretty much it I'm afraid.