paginate_by_sql for Rails: a database independent approach

Like many people, I needed a paginate_by_sql for Rails-- a variant of paginate that would allow pagination for arbitrary SQL; something that would work with Oracle and scale to large result sets. This functionality, surprisingly enough, isn't part of the built in Rails framework.

I found several existing solutions on the web, but they either were either hard-coded for Mysql SQL syntax or inefficient, fetching the entire result set then throwing away rows outside the window.

Fortunately all of the needed machinery to do a database independent paginate_by_sql is available underneath the covers in ActiveRecord. The following code adds a paginate_by_sql call to ActionController::Base and a find_by_sql_with_limit method to ActiveRecord::Base; just add it to the end of application.rb.

[Update: The code below now includes improvements from Laurel's blog. It allows the sql to be passed in with an argument list by using sanitize_sql, and it allows an optional :count argument to specify (as an optimization) an integer or query to use for computing the total count.]


module ActiveRecord
    class Base
        def self.find_by_sql_with_limit(sql, offset, limit)
            sql = sanitize_sql(sql)
            add_limit!(sql, {:limit => limit, :offset => offset})
            find_by_sql(sql)
        end

        def self.count_by_sql_wrapping_select_query(sql)
            sql = sanitize_sql(sql)
            count_by_sql("select count(*) from (#{sql}) as my_table")
        end
   end
end

class ApplicationController < ActionController::Base
    def paginate_by_sql(model, sql, per_page, options={})
       if options[:count]
           if options[:count].is_a? Integer
               total = options[:count]
           else
               total = model.count_by_sql(options[:count])
           end
       else
           total = model.count_by_sql_wrapping_select_query(sql)
       end

       object_pages = Paginator.new self, total, per_page,
            @params['page']
       objects = model.find_by_sql_with_limit(sql,
            object_pages.current.to_sql[1], per_page)
       return [object_pages, objects]
   end
end

Using paginate_by_sql is very much like using find_by_sql except that it returns a pair consisting of the paginator object and an array of the results. In the somewhat contrived example below, let's suppose we wanted a paged report that included selected columns from two tables, paged 20 records at a time. The code in the controller would look as follows.

sql = "select j.id, j.name,c.name as company_name from jobs j inner join companies on j.company_id=c.id order by j.activated_date DESC" @job_pages, @jobs = paginate_by_sql Job, sql, 20

The markup in the view is exactly the same as it would be for standard pagination.

PLAIN TEXT

HTML:

<%= pagination_links @job_pages %>
<% for job in jobs %>
<%= job.name %> <%= job.company_name %> <br>
<% end %>

35 Comments so far
Leave a comment

Coincidentally I needed to use this today :)

I added support for bind variables for the sql statement using sanitize_sql. I had to add another method to AR::Base because sanitize_sql is annoyingly protected.


module ActiveRecord
    class Base
        def self.find_by_sql_with_limit(sql, offset, limit)
            sql = sanitize_sql(sql)
            add_limit!(sql, {:limit => limit, :offset => offset})
            find_by_sql(sql)
        end

        def self.count_by_sql_wrapping_select_query(sql)
            sql = sanitize_sql(sql)
            count_by_sql("select count(*) from (#{sql})")
        end
   end
end

class ApplicationController < ActionController::Base
    def paginate_by_sql(model, sql, per_page, options={})
       if options[:count]
           if options[:count].is_a? Integer
               total = options[:count]
           else
               total = model.count_by_sql(options[:count])
           end
       else
           total = model.count_by_sql_wrapping_select_query(sql)
       end

       object_pages = Paginator.new self, total, per_page,
            @params['page']
       objects = model.find_by_sql_with_limit(sql,
            object_pages.current.to_sql[1], per_page)
       return [object_pages, objects]
   end
end

Looks like your blog ate my formatting and half of my comment, so go here:

http://blog.gorgorg.org/articles/2006/06/06/paginate_by_sql

Indeed, there are a number of rather important AR::Base methods that are annoying protected, add_limit! and and sanitize_sql being prime examples.

Hopefully in future versions of ActiveRecord some of these will be exposed for general use and we won’t have to keep cracking open the class.

Sorry about the blog devouring portions of your comment. I patched it up based on your blog posting.

I am also going to update the blog post to incorporate and link to your improvements.

[…] # paginate by sql # http://thebogles.com/blog/2006/06/paginate_by_sql-for-rails-a-more-general-approach/ # added support for sql with arguments # added a :count option for passing in either a Integer count or count query. module ActiveRecord class Base def self.find_by_sql_with_limit(sql, offset, limit) sql = sanitize_sql(sql) add_limit!(sql, {:limit => limit, :offset => offset}) find_by_sql(sql) end def self.count_by_sql_wrapping_select_query(sql) sql = sanitize_sql(sql) count_by_sql("select count(*) from (#{sql})") end end end class ApplicationController < ActionController::Base def paginate_by_sql(model, sql, per_page, options={}) if options[:count] if options[:count].is_a? Integer total = options[:count] else total = model.count_by_sql(options[:count]) end else total = model.count_by_sql_wrapping_select_query(sql) end object_pages = Paginator.new self, total, per_page, @params[’page‘] objects = model.find_by_sql_with_limit(sql, object_pages.current.to_sql[1], per_page) return [object_pages, objects] end end […]

Pardon me for asking a stupid question, but do you have an example of how this code is used in the controller and view? I’m just having a hard time tying it all together.

Examples have been added.

Thanks for the code, works well and provided exactly what I was looking for.
I had to modify the count sql line though to something like

count_by_sql("select count(*) from (#{sql}) as my_table")

beacuse i was getting a sql error telling me that “Every derived table must have its own alias”

This is awesome. It would have been a nightmare to figure out on my own. Thanks.

How exactly do you put this into your application? I tried putting it into the end of application.rb to no avail.. thanks

Nevermind, my previous comment….

But, I did find an issue… it wasn’t working on MYSQL 5. Was getting the following error:

Mysql::Error: Every derived table must have its own alias:

Anyway, I changed the following line of code in count_by_sql_wrapping_select_query method:

count_by_sql(”select count(*) from (#{sql}) as x”)

Resolves the issue.

Where exactly should the following code go?:
—-
module ActiveRecord
class Base
—

Adding them in Application.rb gives a dependency error?

Thx.

Perfect! Absolutely perfect! (Well, I did have to add that small bit to make MySQL 5 happy…)

I had tried implementing someones paginate_from_sql (see http://www.bigbold.com/snippets/posts/show/13) but the total pages counter was dependent on using *_count, something my database structure couldn’t accommodate easily. (see AWDWR 18.9 “Counters”) I was nearly forced to re-structure/re-think/create new tables…

Thank you, Phil! You saved my sanity! (My marriage, my hair-line, etc.)

-Roger A

Tom: You paste the whole shebang, verbatim, at the end of your application.rb, after the final ‘end’.

Sorry, it was paginate_association that was killing me. (see http://www.meepr.com/2006/08/02/rails-pagination-with-associations/)

It’s a freakin’ forest of suggestions and solutions out there. Phil, yours is the clearest, most direct and, most importantly, compatible with what I was dealing with!

I like being able to write and _use_ complex SQL-statements that make up for RoR’s shortcomings in this area.

Cheers!

[…] paginate_by_sql for Rails: a database independent approach (tags: programming ruby rails) […]

[…] I was initially going to use paginate_by_sql to solve this, but further reading let me to include two more parameters to my paginate call. I now have :select and :conditions set. […]

I tried copy/pasting it into application.rb, but I came up with the error “uninitialized constant Base.” Anyone else have this problem?

# I found a bug, added _as temp_

module ActiveRecord
class Base
def self.find_by_sql_with_limit(sql, offset, limit)
sql = sanitize_sql(sql)
add_limit!(sql, {:limit => limit, :offset => offset})
find_by_sql(sql)
end

def self.count_by_sql_wrapping_select_query(sql)
sql = sanitize_sql(sql)
count_by_sql(”select count(*) from (#{sql}) as temp”)
end
end
end

Is a certain version of mysql required for the subselect? I get the following:

mysql> select count(*) from (select * from targets);
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ’select * from targets)’ at line 1
mysql> select count(*) from (select * from targets) as temp;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ’select * from targets) as temp’ at line 1
mysql>

Thank you very much for your solution. You saved me a lot of work. I have one concern, though. If I do not pass a value for :count, method count_by_sql_wrapping_select_query turns my “normal” query (which contains a field selection list and an ORDER BY clause) into a subquery (e.g., “select count(*) from (SELECT c.active, c.atype FROM categories …)”) where a simple “select count(*) from categories …)” would have sufficed. Of course, I could fix this by calling a simplified count method and then passing :count.
Instead I changed your code so that I pass two pieces of SQL code, the first one containing my complete query with field list and ORDER BY, the second one containing only the part that is needed for the counting (I had to remove the parentheses around “#{sql}” from “select count(*)” to make this work…
Please let me know what you think of this approach. Totally stupid???

Thanks this was what i need :-).

Note in the plain text file an @ should be added
2.

I’m still using mysql 3.23 so subqueries aren’t supported. To work around the
count_by_sql("select (*) from (#{sql}) as my_table") I changed it to (self.find_by_sql("#{sql}")).size.

Now i’m pretty new to ruby/rails, is there anything inherently wrong with doing this?

(self.find_by_sql(”#{sql})).size will work, but it’s much less efficient.

(self.find_by_sql(”#{sql}”)) will fetch the entire result set and turn it into ActiveRecord objects.

I’m receiving

undefined method `count_by_sql_wrapping_select_query’ for :Hosting:Symbol

Any ideas chaps?

When i put the code in the final part of the aplication.rb i got this error

undefined method `count_by_sql_wrapping_select_query’ for

i dont know whats the problem

Ben/Mania,

I got the same thing ‘count_by_sql_wrapping_select_query’. It was because I was referencing my model as ‘:person’, rather than ‘Person’.

You refer to it as ‘:person’ when using the built in pagination and ‘Person’ using paginate_by_sql.

I hope this helps…

Hey Thx ,This is one is good .It works for me but with a minor change in count_by_sql_wrapping_select_query method.Just add “as subquery_of_count” as below

def self.count_by_sql_wrapping_select_query(sql)
sql = sanitize_sql(sql)
count_by_sql(”select count(*) from (#{sql}) as subquery_of_count”)
end

[…] J’ai eu le même besoin il y a peu de temps, mais ma problèmatique comprenait un paramètre supplémentaire. A savoir mon tableau était lui même composé de données venant de deux tables différentes. Or la fonction paginate de Rails ne sait par défaut que travailler sur une seule table. Heureusement une solution existe via la méthode paginate_by_sql dont vous trouverez les détails sur le blog de Phil Bogle. Faisons donc une adaptation sur la base du tutoriel de Julien… • • • […]

I’m using Oracle and getting the following error..

TypeError in HomeController#index

allocator undefined for Float
RAILS_ROOT: /prod/www/911Page/public/../config/..

Application Trace | Framework Trace | Full Trace
#{RAILS_ROOT}/vendor/rails/activerecord/lib/active_record/query_cache.rb:62:in `dup’
#{RAILS_ROOT}/vendor/rails/activerecord/lib/active_record/query_cache.rb:62:in `cache’
#{RAILS_ROOT}/vendor/rails/activerecord/lib/active_record/query_cache.rb:25:in `select_value’
#{RAILS_ROOT}/vendor/rails/activerecord/lib/active_record/base.rb:519:in `count_by_sql’
#{RAILS_ROOT}/app/controllers/application.rb:15:in `count_by_sql_wrapping_select_query’
#{RAILS_ROOT}/app/controllers/application.rb:29:in `paginate_by_sql’
#{RAILS_ROOT}/app/controllers/home_controller.rb:11:in `index’

Any ideas??

thanks in advance.

MySQL doesn’t like subqueries that have two columns with the same name. So if your SQL joins up two tables that have a common column name, the existing count_by_sql_wrapping_select_query call will fail. A simple workaround is to replace it with the following. This removes the subquery issues entirely:

def self.count_by_sql_wrapping_select_query(sql)
sql = sanitize_sql(sql)
sql = sql.sub(/SELECT .* FROM/, “SELECT COUNT(*) FROM” )
count_by_sql(sql)
end

just want to say THANKS :-)

I get the following error in the Paginator statement in paginate_by_sql class

“You have a nil object when you didn’t expect it!
You might have expected an instance of ActiveRecord::Base.
The error occurred while evaluating nil.[]”

Can you please help me resolve this error? Thanks.

I tried using this….but got an error…
uninitialized constant ApplicationController::Paginator

Do I need to install a pagination plugin(classic/will_paginate) as well????

I pasted the pagination_helper.rb file in my /app/helpers……but getting d same error…

Plz someone suggest where am I going wrong???
THANKS….

The reason it doesn’t work is because of the changes in rails 2. Thankfully the will_paginate plugin has included this facility method paginate_by_sql method in rails 2 inside vendor/plugins/will_paginate/lib/will_paginate/finder.rb. We just have to write Model.paginate_by_sql [sql], followed by the usual options such as :page and :per_page.

I get the same error as ZEBA with Rails 2.3.2. But I’m slightly confused as to how to modify this paginate_by_sql code, to solve it.

I’d really appreciate the help, thanks!

Leave a comment

Phil Bogle

Email:
IM:
Phone:	call me

Mon	Tue	Wed	Thu	Fri	Sat	Sun
« May				Jul »
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30

paginate_by_sql for Rails: a database independent approach

Phil Bogle

Search this Blog

Categories

Calendar

Archives

Recent Del.icio.us Links