In this part one of the two-article series on pagination, we will learn how to properly implement offset paging and cursor paging using SQL or Knex

Offset

It is the most popular and easy-to-implement paging technique. The offset-pagination is used when we want to give users an option to jump to specific pages.

To implement offset pagination we need to write two queries – one to fetch records and the other to get the total count. We also require two inputs from the user – the number of records per page and the page number.

Suppose, we want to fetch artists from the MusicBrainz database and paginate the data.  To achieve that we would use LIMIT and OFFSET clause as shown in the steps below.

  • The first step would be to fetch the specific page using perPage and page parameters provided by the user. For example, with page = 4 and perPage = 10, we need to skip the first 30 records and then fetch the subsequent 10 records to get the fourth page of results. 
    
SELECT * FROM musicbrainz.artist 
ORDER BY ID asc OFFSET 30 LIMIT 10;


    const artists = this.knexPg('artist')
  .select('id', 'name')
  .orderBy('id', 'asc')
  .limit(perPage)
  .offset((page - 1) * perPage);
  • We then count all of the artists to determine the total number of pages. 
    SELECT COUNT(*) FROM musicbrainz.artist
    
const count = await this.knexPg('artist')
  .count({ total: '*' })
  .first();
const total = count.total;
const totalPages = Math.ceil(total / perPage);
  • Here’s the full code for the offset pagination function and a sample response: 
    
async getArtists(perPage, page) {
  const artists = await this.knexPg('artist')
    .select('id', 'name')
    .orderBy('id', 'asc')
    .limit(perPage)
    .offset((page - 1) * perPage);
  const count = await this.knexPg('artist').count({ total: '*'}).first();
  const total = parseInt(count.total);
  const totalPages = Math.ceil(total / perPage);
  return {
    artists: artists,
    pagination: {
      perPage: perPage,
      currentPage: page,
      totalPages: totalPages,
      total: total,
   },
 };
}
    
{
  "artists": [...],
  "pagination": {
    "perPage": 10,
    "currentPage": 2,
    "totalPages": 194233,
    "total": 1942322
   }
}

Cursor

This paging technique works by making use of WHERE and ORDER BY clauses on a unique, sequential column(s). We fetch data from the table and then return a cursor that the user can send in the next request to fetch the subsequent data.

Unlike offset pagination, we can’t jump to a specific page as there is no such concept in this technique. We can only fetch the first page, the previous page, the next page, and the last page.

In this method, we require the following four inputs from the user:

  • no of records per page
  • the value of the cursor
  • the sort order of the cursor – asc | desc
  • direction – next to fetch items after the cursor or prev to fetch items before the cursor

Now let us fetch the artists again from the MusicBrainz database but this time using cursor pagination. To begin with cursor pagination, we need to first choose a cursor – a column that is both sequential and unique. For this example, we would choose ID column as the cursor.

The ID column can sort the data in ascending or descending order and the user can request the next page or the previous page. So there are a total of four scenarios that we need to handle.

  • order by is asc and direction is next
  • order by is asc and direction is prev
  • order by is desc and direction is next
  • order by is desc and direction is prev

Let us handle each case one by one:

1. Order – ASC and Direction – NEXT

This is pretty straightforward, for example with cursor = 10, perPage = 10, we would write the following query to fetch the next ten records.

In all the four cases, we set LIMIT = perPage + 1 to check if there is a next/prev page.

SELECT * FROM musicbrainz.artist 
WHERE ID > 10 
ORDER BY ID asc 
LIMIT 11;

If cursor = null, it means the user is requesting the first page, so the query would be:

SELECT * FROM musicbrainz.artist 
ORDER BY ID asc 
LIMIT 11;
  • If result.length = perPage or result.length < perPage, it means there is no next page.
  • If result.length > perPage, it means there is a next page and there is an extra item in the result.

The ID of the last item from the result will be returned to the user as next_cursor but if there is a next page, we need to first remove that extra last item from the result.

2. Order – ASC and Direction – PREV

You must be thinking that flipping the comparison operator in the previous query is all we need to traverse in the opposite direction. Let us try that to see if it works.

SELECT * FROM musicbrainz.artist 
WHERE ID < 11 
ORDER BY ID asc 
LIMIT 11;

We should the get records from 1-10. It did work but is it right? Now, let us assume the cursor = 21 and then run the query.

SELECT * FROM musicbrainz.artist 
WHERE ID < 21 
ORDER BY ID asc 
LIMIT 11;

The query should have output records 10-20 but instead, we get records 1-11, but why? Because we forgot that the data is in ascending order and begins with ID = 1 and it is also less than 21.

So, to get the correct records, we need to flip the ORDER of the cursor.

SELECT * FROM musicbrainz.artist 
WHERE ID < 21 
ORDER BY ID desc 
LIMIT 11;

results = [
    { id: 20 } ... { id:10 }
]

The data is correct but the order is incorrect. To fix this, just reverse the results array.

  • If cursor = null, it means the user is requesting the last page, so the query would be: SELECT * FROM musicbrainz.artist ORDER BY ID desc LIMIT 11;
  • If result.length = perPage or result.length < perPage, it means there is no prev page.
  • If result.length > perPage, it means there is a prev page and there is an extra item in the result.

The ID of the first item in the result will be returned to the user as the prev_cursor but if there is a prev page, we need to first remove that extra item from the top.

3. Order – DESC and Direction – NEXT

The only difference between this case and the first case is the order. So, we just need to change the comparison operator from > to < and ORDER BY from asc to desc and the rest is exactly the same.

SELECT * FROM musicbrainz.artist 
WHERE ID < 10 
ORDER BY ID desc 
LIMIT 11;

4. Order – DESC and Direction – PREV

Change the comparison operator from < to > and flip the ORDER BY from ‘desc’ to ‘asc’ from the third case and follow the rest.

SELECT * FROM musicbrainz.artist 
WHERE ID > 91 
ORDER BY ID asc 
LIMIT 11;

Here’s the full code for the offset pagination function and a sample response

 public static async cursorPaginate(params: ICursorPaginateParams) {
    const { query, cursor, perPage = 10, dataKey = 'data' } = params;
    const cursorMeta = {
      hasNextPage: false,
      next_cursor: null,
      hasPrevPage: false,
      prev_cursor: null,
    };
    const whereOperator = this.getWhereOperator(cursor.order, cursor.direction);
    // if cursor is null, we need to get the first/last page
    if (cursor.value) {
      query.where(cursor.key, whereOperator, cursor.value);
    }
    // if direction is prev, we need to reverse the order
    const order =
      cursor.direction === 'next'
        ? cursor.order
        : cursor.order === 'asc'
        ? 'desc'
        : 'asc';
    // add +1 to the limit to determine if there is a next/prev page
    const result = await query.orderBy(cursor.key, order).limit(perPage + 1);
    // if direction is prev, we need to reverse the result
    if (order !== cursor.order) {
      result.reverse();
    }
    // if we have more than perPage results, we have a next/prev page
    if (result.length > perPage) {
      if (cursor.direction === 'next') {
        result.pop();
        cursorMeta.hasNextPage = true;
        if (cursor.value) {
          cursorMeta.hasPrevPage = true;
          cursorMeta.prev_cursor = result[0][cursor.key];
        }
        cursorMeta.next_cursor = result[result.length - 1][cursor.key];
      } else {
        result.shift();
        cursorMeta.hasPrevPage = true;
        if (cursor.value) {
          cursorMeta.hasNextPage = true;
          cursorMeta.next_cursor = result[result.length - 1][cursor.key];
        }
        cursorMeta.prev_cursor = result[0][cursor.key];
      }
    } else if (result.length > 0 && result.length <= perPage) {
      if (cursor.direction === 'next') {
        if (cursor.value) {
          cursorMeta.hasPrevPage = true;
          cursorMeta.prev_cursor = result[0][cursor.key];
        }
      } else {
        if (cursor.value) {
          cursorMeta.hasNextPage = true;
          cursorMeta.next_cursor = result[result.length - 1][cursor.key];
        }
      }
    }

    return {
      [dataKey]: result,
      pagination: {
        cursor: cursorMeta,
        per_page: perPage,
      },
    };
  }

  private static getWhereOperator(order, direction) {
    if (order === 'asc') {
      if (direction === 'next') {
        return '>';
      } else {
        return '<';
      }
    } else {
      if (direction === 'prev') {
        return '>';
      } else {
        return '<';
      }
    }
  }

{
    "artists": [...],
    "pagination": {
        "cursor": {
            "hasNextPage": true,
            "next_cursor": 45,
            "hasPrevPage": true,
            "prev_cursor": 41
        },
        "per_page": 10
    }
}

In the next post of this series, we will discuss their pros and cons and benchmark both of these pagination methods to compare their performances using the MusicBrainz database.

Categorized in:

Database

Tagged in:

, ,