module WillPaginate::ActiveRecord::BaseMethods

Public Instance Methods

paginate_by_sql(sql, options) click to toggle source

Wraps find_by_sql by simply adding LIMIT and OFFSET to your SQL string based on the params otherwise used by paginating finds: page and per_page.

Example:

@developers = Developer.paginate_by_sql ['select * from developers where salary > ?', 80000],
                       :page => params[:page], :per_page => 3

A query for counting rows will automatically be generated if you don't supply :total_entries. If you experience problems with this generated SQL, you might want to perform the count manually in your application.

# File lib/will_paginate/active_record.rb, line 197
      def paginate_by_sql(sql, options)
        pagenum  = options.fetch(:page) { raise ArgumentError, ":page parameter required" } || 1
        per_page = options[:per_page] || self.per_page
        total    = options[:total_entries]

        WillPaginate::Collection.create(pagenum, per_page, total) do |pager|
          query = sanitize_sql(sql.dup)
          original_query = query.dup
          oracle = self.connection.adapter_name =~ /^(oracle|oci$)/i

          # add limit, offset
          if oracle
            query = <<-SQL
              SELECT * FROM (
                SELECT rownum rnum, a.* FROM (#{query}) a
                WHERE rownum <= #{pager.offset + pager.per_page}
              ) WHERE rnum >= #{pager.offset}
            SQL
          else
            query << " LIMIT #{pager.per_page} OFFSET #{pager.offset}"
          end

          # perfom the find
          pager.replace find_by_sql(query)

          unless pager.total_entries
            count_query = original_query.sub /\bORDER\s+BY\s+[\w`,\s.]+$/mi, ''
            count_query = "SELECT COUNT(*) FROM (#{count_query})"
            count_query << ' AS count_table' unless oracle
            # perform the count query
            pager.total_entries = count_by_sql(count_query)
          end
        end
      end