Paginate Activerecord Results Without A Gem Using Limit And Offset
Kaminari and Pagy are excellent, but a JSON API or an internal admin screen often needs nothing more than a page number and a page size. ActiveRecord gives you that in two methods.
Description
limit sets the page size and offset skips the preceding pages. The arithmetic is offset = (page - 1) * per_page.
Always clamp the inputs. An unbounded per_page from a query string lets anyone request a million rows, and a negative page produces a negative offset and a SQL error.
Always add a deterministic order. Without one, PostgreSQL makes no ordering guarantee between queries, so rows can appear on two pages or none.
Note that OFFSET gets slower as the offset grows, because the database still scans the skipped rows. Past a few thousand pages, switch to keyset pagination on an indexed column.
Sample input:
Order.page_of(page: 3, per_page: 25)
Sample Output:
SELECT orders.* FROM orders ORDER BY created_at DESC, id DESC LIMIT 25 OFFSET 50
Answer
class ApplicationRecord < ActiveRecord::Base
self.abstract_class = true
MAX_PER_PAGE = 100
scope :page_of, ->(page: 1, per_page: 25) {
page = [page.to_i, 1].max
per_page = per_page.to_i.clamp(1, MAX_PER_PAGE)
limit(per_page).offset((page - 1) * per_page)
}
end
# Usage — note the explicit, deterministic order
orders = Order.order(created_at: :desc, id: :desc)
.page_of(page: params[:page], per_page: params[:per_page])
# Pagination metadata for the response
total = Order.count
render json: {
data: orders.as_json,
meta: {
page: page,
per_page: per_page,
total: total,
total_pages: (total / per_page.to_f).ceil
}
}
# Keyset pagination — constant time regardless of depth
scope :after_cursor, ->(created_at, id) {
where("(created_at, id) < (?, ?)", created_at, id)
.order(created_at: :desc, id: :desc)
}
Check viewARU - Brand Newsletter!
Newsletter to DEVs by DEVs - boost your Personal Brand & career! 🚀
