Want to stay on top? Ruby Weekly is a once-weekly e-mail newsletter covering the latest Ruby and Rails news.
     Feed Icon

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

  1. Hugo Villero says:

    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

  2. Paul Battley says:

    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")

  3. engel says:

    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

  4. Binsu says:

    Where should the above code go? or in which file should this be added, directly in the ActiveRecord or in Application.rb?

  5. Peter Cooper says:

    The latter, or it could be in a file in /lib that's then required in from environment.rb.

  6. Binsu says:

    Adding the ActiveRecord injection in Application.rb gives an error.
    ----
    module ActiveRecord
    class Base
    ....
    end
    ----
    Will update after trying in a lib directory.

  7. engel says:

    Well, I already found a solution, I just added :order => 'id ASC', and it works now

Other Posts to Enjoy

Twitter Mentions