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 Coleman -- Posted via http://www.ruby-forum.com/.
Mr Coleman: On 4/12/06, Jeff Coleman <progressions@gmail.com> 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 -- Cheers, Hasan Diwan <hasan.diwan@gmail.com>
Hasan Diwan wrote:> Mr Coleman: > > On 4/12/06, Jeff Coleman <progressions@gmail.com> 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 > endThat seems to do the trick! Thanks. I''m going to study this example closely. Jeff -- Posted via http://www.ruby-forum.com/.
> On Thu, 13 Apr 2006, Hasan Diwan wrote: > > > Mr Coleman: > > On 4/12/06, Jeff Coleman <progressions@gmail.com> 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 > endI''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
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 On Apr 13, 2006, at 4:44 PM, Philip Hallstrom wrote:>> On Thu, 13 Apr 2006, Hasan Diwan wrote: >> > Mr Coleman: >> On 4/12/06, Jeff Coleman <progressions@gmail.com> 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''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 > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails
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 < blasp.hemo.us-ruby-forum-account@blasp.hemo.us> wrote:> > 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 > > > On Apr 13, 2006, at 4:44 PM, Philip Hallstrom wrote: > > >> On Thu, 13 Apr 2006, Hasan Diwan wrote: > >> > Mr Coleman: > >> On 4/12/06, Jeff Coleman <progressions@gmail.com> 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''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 > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060414/203fb69d/attachment.html
On 4/12/06, Jeff Coleman <progressions@gmail.com> wrote:> 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? >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.
Hasan Diwan wrote:> Mr Coleman: > > On 4/12/06, Jeff Coleman <progressions@gmail.com> 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 > endI 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 Coleman -- Posted via http://www.ruby-forum.com/.