Topic: Pagination Idea with Scopes and Blocks

This is a rant on the current pagination and some ideas on how to improve it. Feel free to skip it if this stuff doesn't interest you. smile

---

I've been thinking about pagination lately. I like to move complex finds into the model but the current pagination in Rails makes this difficult. It requires you create the Paginator object manually. Then you have the classic problem of needing the total count of results found without the per-page limit so the paginator can calculate the total number of pages. This usually requires you run two queries (one for the count and one for the items) which leads to all kinds of duplication of search conditions and more problems.

MySQL allows you to set "SQL_CALC_FOUND_ROWS" in a SELECT clause to calculate the total number of rows (without the limit) the same time you are fetching the limit. This is excellent to give you a performance boost, but it's not availabe to all database engines. This tells me this should be handled in the connection adapter.

I think the Rails "find" method can be extended to offer an option (say ":calc_total => true") where this will automatically add the SQL_CALC_FOUND_ROWS tag in the find if it's on MySQL. If it's on another database engine it can just run the find again (with the same conditions) and do a COUNT(*) without the limit to fetch the total count. Either way, the total count can be stored in a class variable which can later be retrieved something like "MyModel.total_count_for_previous_query".

I thought there was a blog post describing how to implement what I'm talking about, but I can't find it now.

How will moving the total calculation into the "find" method help pagination? Two words: with_scope. Using scoping in the paginator will allow you to set the ":limit" and the new ":calc_total" options for the find method. This means we can use blocks with the paginate method.

@paginator, @users = paginate(10) { User.find_all_by_active(true) }

Wow, what is really happening here? The "paginate" method only needs one argument - the number of results you want per page (assuming it can find the current page in params[:page]). The paginate method makes a find scope setting the :limit and :calc_total find options. When the block is run inside this scope the custom find method inherits this scope and everything is calculated. The paginate method grabs the calculated total from the find, creates a paginator type of object and returns that along with the result of the block. If you don't like using multiple assignment you can do this instead:

@paginator = paginate(10) { @users = User.find_all_by_active(true) }

In both cases, the User.find_all_by_active() method is just an example. You can use any kind of "find" method you want - it can be inside the model or anywhere because the scope handles it.

I have not implemented this yet, so I dont' even know if it will work. If you have any comments, or want to take my idea and run with it, feel free to do so.

Railscasts - Free Ruby on Rails Screencasts