paginate_by_sql: Rails pagination on your own SQL queries

By Peter Cooper / June 6, 2006

Pagination in Rails is good, but it can lack flexibility in many situations. At that point it's time to roll your own. However, Phil Bogle and Laurel Fan came up with a solution they call paginate_by_sql that can solve some of the custom pagination problems. This needs to become a plugin.

I'm reposting their code here simply because I like to see syntax coloring :)

# paginate by sql
# http://thebogles.com/blog/2006/06/paginate_by_sql-for-rails-a-more-general-approach/
# added support for sql with arguments
# added a :count option for passing in either a Integer count or count query.
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})")
        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

Comments

Hugo Villero says:
July 10, 2006 at 6:57 pm
Hi There, that yellow part in the code about:

"count_by_sql("select count(*) from (#{sql})")"

doesn't work for me I'm using Microsoft SQL Server but any wasy I got this error :

DBI::DatabaseError: Execute
OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
Line 1: Incorrect syntax near ')'.
HRESULT error code:0x80020009
Exception occurred.: select count(*) from (select g.systemid,g.groupname from groups g where groupname = 'AD')

Could ypu please help me with some view code running on sql server?

Thanks so much

hugo villero
Paul Battley says:
July 12, 2006 at 7:56 am
I had to change one line slightly to work on MySQL (and, although I didn't test it, I think the same is true for PostgreSQL):

count_by_sql("select count(*) from (#{sql}) as subquery_for_count")
engel says:
July 19, 2006 at 9:25 am
Good Day!

Can anyone help me, I'm working with SQL Server as database and it seems tha t pagination doeasn't work, although the links are displayed and the url changes after click (something like page=x) the page itself doesn't change and (i.e. the record display stay as it is.

Can anyone help me. Thank You!
Engel
Binsu says:
August 11, 2006 at 4:20 pm
Where should the above code go? or in which file should this be added, directly in the ActiveRecord or in Application.rb?
Peter Cooper says:
August 11, 2006 at 11:56 pm
The latter, or it could be in a file in /lib that's then required in from environment.rb.
Binsu says:
August 12, 2006 at 8:20 am
Adding the ActiveRecord injection in Application.rb gives an error.
----
module ActiveRecord
class Base
....
end
----
Will update after trying in a lib directory.
engel says:
August 20, 2006 at 11:09 am
Well, I already found a solution, I just added :order => 'id ASC', and it works now