Like many people, I needed a paginate_by_sql for Rails-- a variant of paginate that would allow pagination for arbitrary SQL; something that would work with Oracle and scale to large result sets. This functionality, surprisingly enough, isn't part of the built in Rails framework.

I found several existing solutions on the web, but they either were either hard-coded for Mysql SQL syntax or inefficient, fetching the entire result set then throwing away rows outside the window.

Fortunately all of the needed machinery to do a database independent paginate_by_sql is available underneath the covers in ActiveRecord. The following code adds a paginate_by_sql call to ActionController::Base and a find_by_sql_with_limit method to ActiveRecord::Base; just add it to the end of application.rb.

[Update: The code below now includes improvements from Laurel's blog. It allows the sql to be passed in with an argument list by using sanitize_sql, and it allows an optional :count argument to specify (as an optimization) an integer or query to use for computing the total count.]

module ActiveRecord
    class Base
        def self.find_by_sql_with_limit(sql, offset, limit)
            sql = sanitize_sql(sql)
            add_limit!(sql, {:limit => limit, :offset => offset})
            find_by_sql(sql)
        end

        def self.count_by_sql_wrapping_select_query(sql)
            sql = sanitize_sql(sql)
            count_by_sql("select count(*) from (#{sql}) as my_table")
        end
   end
end

class ApplicationController < ActionController::Base
    def paginate_by_sql(model, sql, per_page, options={})
       if options[:count]
           if options[:count].is_a? Integer
               total = options[:count]
           else
               total = model.count_by_sql(options[:count])
           end
       else
           total = model.count_by_sql_wrapping_select_query(sql)
       end

       object_pages = Paginator.new self, total, per_page,
            @params['page']
       objects = model.find_by_sql_with_limit(sql,
            object_pages.current.to_sql[1], per_page)
       return [object_pages, objects]
   end
end

Using paginate_by_sql is very much like using find_by_sql except that it returns a pair consisting of the paginator object and an array of the results. In the somewhat contrived example below, let's suppose we wanted a paged report that included selected columns from two tables, paged 20 records at a time. The code in the controller would look as follows.

sql = "select j.id, j.name,c.name as company_name from jobs j inner join companies on j.company_id=c.id order by j.activated_date DESC"

@job_pages, @jobs = paginate_by_sql Job, sql, 20

The markup in the view is exactly the same as it would be for standard pagination.