Hasan D. wrote:
Mr Coleman:
On 4/12/06, Jeff C.man [email protected] wrote:
I’m not sure how to paginate this query. Is it possible? Is there a
simpler way to filter out all the houses without an associated image
from a search?
Use the following (untested) code in application.rb:
def find_by_sql_pagination(items_per_page,sql,table)
page = (params[:page] ||= 1).to_i
offset = (page - 1) * items_per_page
@results=table.find_by_sql(sql)
@pages=Paginator.new(self, @results.length, items_per_page, page)
@results=@results[offset…(offset + items_per_page - 1)]
return @pages,@results
end
I studied this example and then went through the source code for
paginate, and found a way to overload paginate and add a :sql option. I
put this in my application.rb file:
this is the paginate object’s default options hash, setting defaults
DEFAULT_OPTIONS[:sql] = nil
def find_collection_for_pagination(model, options, paginator)
unless options[:sql].nil?
options[:sql][0] += " order by #{options[:order_by] ||
options[:order]} " unless (options[:order_by].nil? &&
options[:order].nil?)
options[:sql][0] += " limit #{options[:per_page]} offset
#{paginator.current.offset} "
model.find_by_sql(options[:sql])
else
super
end
end
This probably isn’t complete, but it allows a basic use of the paginate
function with a :sql option, like so:
paginate :houses, :sql => [“select distinct h.* from houses h, images i
where h.price < ? and i.house_id = h.id”, params[:price]], :per_page =>
10
I even found the little-documented paginate option :parameter, which
lets you define a different parameter name besides :page to use for the
page number. That’s useful because I have a second pagination object on
the page, which is an image display, and you can page through the images
associated with a house using Ajax.
Anyone have any thoughts? What could I have done better?
I’m curious if there’s a reason Rails’ default paginate method doesn’t
have a :sql option in the first place, it seems fairly straightforward
to implement.
Jeff C.man