Caiwangqin’s blog

在RubyonRails编程时，很多时候为find_by_sql增加paginate功能显得很有用，BOGLE已经实现了一个无关数据库的paginate_by_sql for Rails, 试用后感觉不错。代码如下：

# paginate by sql
# 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,  ffset => 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  

在Controller中的使用如下：

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

在View中使用如下：

Del.icio.us : paginator pagination paginate

Tags: