So you’ve decided to implement cursor pagination in your website. Well, you’ve come to the right place! (If you’re not entirely convinced cursors is right for you, first, check out the benefits in this article to see if it is indeed the right option for you.)

First, you will need a unique, sequential column (e.g. timestamps, usernames, emails, etc.). Keep in mind that the data will be sorted by this column. So if, for instance, you wanted to sort a user list by last name, which is generally not considered a unique column, there will be additional work necessary. (If this is the case, never fear! Scroll to the bottom of this article for some tips to get around this limitation.)

But first, let’s discuss the ideal scenario.

Cursor Implementation

If offset pagination is an array, then cursor pagination is a linked list. Where offset grabs records based on where they are located in the table like an index, cursors use a pointer that points to a specific record and grabs the records following this specific record.

To better understand this concept, let’s take a look at the SQL statements used to generate the first 2 pages of the following user list.

╔═════════════╦═══════════════╗
║  Last Name  ║   First Name  ║   
╠═════════════╬═══════════════╣
║ Bagshot     ║ Bathilda      ║ 
║ Black       ║ Sirius        ║ 
║ Brown       ║ Lavender      ║ 
║ Chang       ║ Cho           ║ 
║ Creevey     ║ Colin         ║ 
║ Crouch      ║ Bartemius     ║ 
║ Delacour    ║ Fleur         ║ 
║ Diggle      ║ Dedalus       ║
║ Diggory     ║ Cedric        ║ 
║ Dumbledore  ║ Aberforth     ║ 
║ Dumbledore  ║ Albus         ║ 
║ Dursley     ║ Dudley        ║
║ Dursley     ║ Petunia       ║
║ Dursley     ║ Vernon        ║
║ Filch       ║ Argus         ║ 
║ Finnigan    ║ Seamus        ║ 
║ Fletcher    ║ Mundungus     ║ 
╚═════════════╩═══════════════╝

With a limit of 5 per page, we would expect the first and second page to look like:

Page 1:
╔═════════════╦═══════════════╗
║  Last Name  ║   First Name  ║   
╠═════════════╬═══════════════╣
║ Bagshot     ║ Bathilda      ║ 
║ Black       ║ Sirius        ║ 
║ Brown       ║ Lavender      ║ 
║ Chang       ║ Cho           ║ 
║ Creevey     ║ Colin         ║ 
╚═════════════╩═══════════════╝
Page 2:
╔═════════════╦═══════════════╗
║  Last Name  ║   First Name  ║   
╠═════════════╬═══════════════╣
║ Crouch      ║ Bartemius     ║ 
║ Delacour    ║ Fleur         ║ 
║ Diggle      ║ Dedalus       ║
║ Diggory     ║ Cedric        ║ 
║ Dumbledore  ║ Aberforth     ║
╚═════════════╩═══════════════╝

The SQL statements to generate each page, respectively:

SELECT * FROM users WHERE "" > last_name ORDER BY last_name LIMIT 5
SELECT * FROM users WHERE "Creevey" > last_name ORDER BY last_name LIMIT 5

In addition to the list of users, the following paging object would also be included in the server response for each page, respectively:

{
    “cursor”: {
        “previous_page”: null,
        “next_page”: "next___Creevey"
    }
}
{
    “cursor”: {
        “previous_page”: "prev___Crouch" ,
        “next_page”: "next___Dumbledore"
    }
}

#cursor #api #pagination #sql