Forum: Ruby on Rails Complex SQL in paginate command?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Jeff C. (Guest)
on 2006-04-13 10:50
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
Hasan D. (Guest)
on 2006-04-13 11:30
(Received via mailing list)
Mr Coleman:

On 4/12/06, Jeff C.man <removed_email_address@domain.invalid> 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
Jeff C. (Guest)
on 2006-04-13 11:37
Hasan D. wrote:
> Mr Coleman:
>
> On 4/12/06, Jeff C.man <removed_email_address@domain.invalid> 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
Philip H. (Guest)
on 2006-04-14 03:44
(Received via mailing list)
> 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
Tres Wong-Godfrey (Guest)
on 2006-04-14 04:30
(Received via mailing list)
Hey Philip,

I know you from SeaBUG. Nice to see a familiar "face" around here. :)

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... :)

Tres
Benjamin G. (Guest)
on 2006-04-14 16:49
(Received via mailing list)
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 <
Wilson B. (Guest)
on 2006-04-14 19:48
(Received via mailing list)
On 4/12/06, Jeff C.man <removed_email_address@domain.invalid> 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.

http://www.bigbold.com/snippets/posts/show/389

--Wilson.
Jeff C. (Guest)
on 2006-04-17 02:37
Hasan D. wrote:
> Mr Coleman:
>
> On 4/12/06, Jeff C.man <removed_email_address@domain.invalid> 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
This topic is locked and can not be replied to.