How to use pagination over database models when using the Sequel database library with the Padrino web framework.
The Padrino web project needs to use Sequel as the ORM/database component. So I assume that you find “:orm: sequel” in your .components file.
Your database configuration must be set in config/database.rb. At the end of the file also add:
Sequel::Model.db.extension(:pagination)
This enables the pagination extension that comes with Sequel. It does the page calculations similar to what pagination modules like will_paginate are doing and returns a set of items to display in a view/template. It does not have functionality to render the paginator in the view so you may still want to use will_paginate to render the navigation in your view/template.
So now any Sequel dataset has a .paginate() method added. Examples:
@page = Sequel::Model.db[:table].paginate(@page, size) @page = MyModel.dataset.paginate(@page, size)
As you see you can’t use “MyModel.paginate()” directly. Opposed to what ActiveRecord does Sequel distinguishes between models and datasets. But you will still get an array of (ORM) objects back that you can manipulate and save back to the database. So all you need to do really is add “.dataset”.
One caveat is that the .paginate(page_nr, number_of_items_per_page) requires the page_nr parameter to be an integer. But the params[] hash that you can use in a Padrino controller contains either “nil” if the parameter is missing or a string. So you need to turn “nil” into 1 (the first page). And any other page like “15” needs to be turned into an integer 15.
So a typical two-liner in your controller looks like:
@page = (params[:page] || 1).to_i @page = MyModel.dataset.paginate(@page, size)
I still wanted to be able to make this shorter and talked to Jeremy Evans – the maker of Sequel and he suggested to add a method to the Sequel::Model class. So if you are as lazy as me then add a file model/paginate.rb and insert:
# These helper methods add a .page method that works for pagination with ORM objects and datasets.
# Basically they save the “page = (page || 1).to_i”.# Define a class method to paginate through ORM models
class Sequel::Model
def self.page(page, per_page=10, *args)
# The page number comes as a string from the Sinatra controller.
# Turn it into an integer and make it 1 if it was nil.
page = (page || 1).to_i
dataset.paginate(page, per_page, *args)
end
end# Define a class method to paginate through datasets
class Sequel::Dataset
def page(page, per_page=10, *args)
# The page number comes as a string from the Sinatra controller.
# Turn it into an integer and make it 1 if it was nil.
page = (page || 1).to_i
paginate(page, per_page, *args)
end
end
This file gets loaded automatically by Padrino. And as your models inherit from the Sequel::Model class and datasets are instances of Sequel::Dataset you get this class method, too. So now in your Padrino controller you can simply write:
@page = MyModel.page(param[:page], 10)
for models or
@page = MyModel.grep(:name, '%foo%').page(param[:page], 10)
for datasets. The reason I call this method “.page” and not “.paginate” is to avoid name clashes with Sequel::Dataset.paginate.
Sequel offers this set of instance variables to @page that you can use:
- page_size → max items on this page
- page_count → number of pages (at least 1)
- current_page → number of this page (starts at 1)
- pagination_record_count → number of all items
- current_page_record_range → [startitem..enditem]
- current_page_record_count → items on this page
- first_page? → true if this is the first page
- last_page? → true if this is the last page
- next_page → number of next page or nil (if last)
- prev_page → number of previous page or nil (if first)
If you want some more help in your views to render the navigation (something like “First | Previous | 1 2 3 4 | Next | Last”) then install will_paginate:
padrino gen plugin will_paginate
And just call <%= will_paginate @page %> in your view/template. will_paginate lets you customize the look and layout of that navigator.
By the way: MyModel.paginate will still use the Sequel pagination and not will_paginate’s. All you use from will_paginate is the <%= will_paginate … %> view helper. will_paginate has functionality built-in that translates Sequel’s pagination to its own convenctions.
Have fun.