DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Paginate On Custom Sql Queries In Rails

  • submit to reddit
        Someone in #rubyonrails wrote this. Forget who!

def paginate_from_sql(model, sql, total, per_page)
  @object_pages = self, total, per_page, @params['page']
  @objects = model.find_by_sql(sql + " LIMIT #{per_page} " +
                               "OFFSET #{@object_pages.current.to_sql[1]}")


Snippets Manager replied on Wed, 2005/12/14 - 5:52am

I've modified this to automatically create the instance variables related to the supplied model. e.g. paginate_from_sql(Artist, sql, count_sql, 10) will generate @artists & @artist_pages def paginate_from_sql(model, sql, count_sql, per_page) plural_model_name = "@#{}".to_sym paginator_name = "@#{}_pages".to_sym self.instance_variable_set(paginator_name,, model.count_by_sql(count_sql), per_page, @params['page'])) self.instance_variable_set(plural_model_name, model.find_by_sql(sql + " LIMIT #{per_page}" + " OFFSET #{self.instance_variable_get(paginator_name).current.to_sql[1]}")) end

Snippets Manager replied on Mon, 2006/01/23 - 10:37pm

You can also simply add a :conditions parameter to your paginate call (similar to the find call), with your query.