Ruby Cursor Pagination (1)

Intro

As consumer expectations ramp up, API performance has never been more important than it is today. It’s a well-known statistic that 53% of web users will abandon a web page if it takes more than 3 seconds to load.
These expectations don’t necessarily line up with the technical requirements of an API. In the era of big data and analytics, APIs are dealing with larger amounts of data in their backend than ever before. To truly stand their ground in today’s digital economy, APIs must be optimized for peak efficiency. API pagination is a key strategy for making sure your APIs run smoothly and effectively.

Implementation

In this article, I am not going to talk about what is cursor pagination? cursor pagination vs offset pagination? why do I need them, what problem is solved by cursor pagination?

But I'm gonna sharing, how we can achieve cursor pagination that meet our product expectation. Our project currently only have mobile android as client and every index per page implement infinite scroll to access all of the items collection which is fit for cursor.

We use postgres as our main database in this project. So below the problem will be solve using active-record. Also, we just cover after cursor here but actually they have same solution for both after and before cursor.

def pager(after:, before: nil, limit: nil, sort: nil)
  # ... initialize parameter
  collection = if after.nil?
                 order(sorter).extending(ActiveRecordRelationMethods).limit(page_limit)
               else
                 custom_expression = create_custom_expression(after, sorted_columns)
                 where(custom_expression).order(sorter).extending(ActiveRecordRelationMethods).limit(page_limit)
               end

  # ...create_paginate_meta
end

In if condition you can tell me that it simple just return first page if cursor not set. Then if cursor defined, return the page after cursor value. For example, cursor = {id: 125} will return page after id 125. Take a notice, value of cursor usually encoded so client and server need to have contract to choose how this cursor should encode/decode. We encode/decode cursor value in base64, eyJpZCI6MTI1fQ== instead of {id: 125}.

Sorting by something other than id is possible by remembering the last value of the field being sorted on. For example, if you’re sorting by title, then the last value is the title of the last record in the page. If the sort value is not unique, then if we used it alone we would potentially be skipping records. For example, assume you have the following products

Id	Title
3	Hat
2	Pants
4	Pants
1	Shoes

Requesting a page size of two sorted by title would return product with ids 3 and 2. To request the next page, just querying by title > “Pants” would skip product 4 and start at product 1.

Id	Title
3	Hat
2	Pants
4	Pants
1	Shoes

Whatever the use case of the client that requests these records, it’s likely to have problems if records are sometimes skipped. The solution is to set a secondary sort column on a unique value, like id, and then remembering both the last value and last id. In that case the query for the second page would look like this:

SELECT *
FROM `products`
WHERE (`products`.`title` > "Pants" 
  OR (`products`.`title` = "Pants" AND `products`.`id` > 2))
ORDER BY `products`.`title` ASC, `products`.`id` ASC
LIMIT 2

Here is our implementation in ruby

def create_custom_expression(cursor_params, sorted_columns)
  decode_cursor_params = JSON.parse(Base64.strict_decode64(cursor_params))
  return arel_table[primary_key].gt(decode_cursor_params[primary_key]) if sorted_columns.blank?

  filter_ordered_columns = filter_with_ordered_columns(decode_cursor_params, sorted_columns)
  filter_primary_key = filter_with_primary_key(decode_cursor_params)

  filter_ordered_columns.or(filter_primary_key)
end

def filter_with_ordered_columns(decode_cursor_params, sorted_columns)
  result = self
  sorted_columns.each_with_index do |(column, type), index|
    result = if index.zero?
               result.arel_table[column].send(AREL_ORDER[type])
             else
               result.or(arel_table[column].send(AREL_ORDER[type]))
             end
  end

  result
end

def filter_with_primary_key(decode_cursor_params)
  result = self

  decode_cursor_params.each_with_index do |(column, value), index|
    result = if index.zero?
               result.arel_table[column].gt(value)
             else
               result.and(arel_table[column].eq(value))
             end
  end

  result
end

Summary

To ensure the query is performant as the number of records increases you need a database index set up on title and id. If an appropriate index is not set up then it could be even slower than using offset pagination. If you wanna try on your own, I already push the code on github.

Still, there is so much improvement we need on the system which is:

1. implement before cursor
2. meta url for first, last, next and previous page
3. collection size
4. error-cases
5. any other? :D ,please commented below if you have any concern or for any system requirement