Alain M. Lafon

Lately I have been working on several projects that condemn me to use Microsofts SQLServer, which has led me to believe that there is something missing in the RoR core to make it really a sound inclusion for business applications. Whilst there are very good implementations for pagination and sortable tables at hand, these won’t work if your database doesn’t have an auto_increment id or you want to use customized SQL code instead of working with the ActiveRecord model; and believe me – there are lots of databases out there that can’t be changed, because other applications need them in order to function. There are also good uses for custom SQL some times when using Oracle or MSSQL, because they feature their very own toolset to optimize queries in a fashion where Ruby just can’t adapt whilst implementing a generic interface for all potential backends.

So I started working on my very own plugin that would help reduce writing redundant code over and over again. I have hosted a demo version right here which should look like this:

As you can see the interface really is straight forward; click the title to sort the column – and click a number or an arrow to move from page to page.

The nice thing is that this implementation should work with about any database format; and any SQL or ActiveRecord statement. Let me show you how much code you’d have to write to implement the table from above:

In the controller you’d fetch the data with something like:

session[:data] = StUser.find(:all, :limit => 600)

or even something as ugly as

session[:data] = StUser.find_by_sql("select top 600 * from st_users")

Now to the interesting part – in the view you will only have to write one single line; p_pagination is generic – meaning it automatically adjusts to the number of columns and the input data types.

<%= p_paginate(session[:data], {:max => 10,
:page => session[:page].to_i},
"login", "MAIL", "FIRSTNAME", "LASTNAME", "id") %>

Just give it the database resultset(session[:data]), the number of rows per page and the attributes that are to be read from the resultset.

Now you have seen the might and the first drawback of p_pagination. All data is currently saved in the session, which means that the whole resultset is held available – many of you will now think this is very inefficient and draws performance and memory and I would be inclined to support your thought; only if databases like MSSQL supported the allmighty LIMIT and OFFSET commands like MySQL does. Since it doesn’t, I have deliberately chosen this path, because the needed workarounds in MSSQL like select top 10 * from users where login not in (select top 5 login from users) will result in a massive table search – and if your legacy database works on four text columns that alltogether emulate a primary key in userspace.. then you will really blast your performance away.
Anyway – storing the resultset in the session is not that bad, either, because RoR supports storing session information back in the database as explained in http://wiki.rubyonrails.org/rails/pages/HowtoChangeSessionStore. However even in the database the space for each session is limited, so this current implementation won’t perform on massive data with thousands of rows – but on smaller data collections it does the job pretty well as you can see.

There’s one more drawback; my code doesn’t seem to work on all browsers properly – I designed it using Firefox3 and IE6 where it just works fine. But I have seen that Safari displays the spinner to the right of the pagination bar while Firefox2 will scramble the bar and render it useless; Opera I haven’t tried yet.

So this is it – I’d like to call this a preview alpha release even though it already went productive on some small systems where it does its job as expected.

In a following post I will explain how to install p_pagination as a plugin.