Complex SQL in paginate command?

Rails
1

Hi all,

Is there a way to create pagination with a complex SQL, more complex
than the :conditions option will support?

I have two databases, houses and images. Houses has_many images, and
each Image belongs_to house.

I’m creating a search engine for the house records and I’d like to be
able to filter out all the houses without any associated images.

So far I’m doing this by constructing a SQL query that goes something
like this–an example using a maximum price:

“select distinct h.* from houses h, images i where price < 1000000 and
i.house_id = h.id order by price desc”

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?

Thanks,
Jeff C.man

2

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

3

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

That seems to do the trick! Thanks. I’m going to study this example
closely.

Jeff

4

Hey Philip,

I know you from SeaBUG. Nice to see a familiar “face” around here. :slight_smile:

Everything would indicate that you are correct, but without the
actual definition of the variable sql, there’s no way of saying for
certain.

Of course if the code does rely on the sql to limit the return query,
then the method has some inherent design problems, I’d say.

But I guess RAM is cheap these days… :slight_smile:

Tres

5

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’m new to rails and have a question about this… let’s say there’s
1,000
rows in his table, but he wants to show 10 at a time.

Isn’t @results=table.find_by_sql(sql) going to return all 1,000 rows?

Thanks!

-philip

6

While it may return all of the results into @results (I haven’t tested
it
myself), he can then break out the number of results he actually needs.

On 4/13/06, Tres Wong-Godfrey <

7

On 4/12/06, Jeff C.man [email protected] wrote:

So far I’m doing this by constructing a SQL query that goes something
like this–an example using a maximum price:

“select distinct h.* from houses h, images i where price < 1000000 and
i.house_id = h.id order by price desc”

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?

Try ‘paginate_collection’ out. I’ve found this to be a handy snippet
when dealing with strange ‘distinct’ queries and whatnot.
Scroll down into the comments for an updated version with some block
syntax.

–Wilson.

8

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