Warning : This website is not updated anymore. Its content surely is outdated.

How to paginate, sort and search a table with Ajax and Rails

Introduction and warnings

Important warning : unfortunately, I don't have the opportunity to work on Rails anymore these days. So some points in this tutorial may be a bit outdated for the most recent versions of the framework.

In this tutorial, we will try to use Ajax with Rails in order to display a table of items with several functionalities :

A live demonstration of this application is available at :

http://dev.nozav.org/ajaxtable/

This is a very common task in web application development. The interest to use Ajax for this is to provide a dynamic interface which doesn't need to reload the entire page when the table changes. The interest of using Rails is... well, if you are reading this you should already know, but Ajax is really nicely integrated with Rails, and using it is very easier with this great framework. Nevertheless, the code in this tutorial should work the traditional way (by reloading the entire page) if javascript is not available on client side. This is very important for accessibility.

The code which follows is highly inspired from several Rails wiki pages, in particular How to make a real-time search box with the Ajax helpers, and How to paginate with Ajax. I just put things together and sometimes slightly adapted the code.

Now, the warnings. I am not a Rails guru, and far from being an Ajax expert. I am a beginner in both areas. So take this document as a beginner introduction to beginners : code could be cleaner, explanations more accurate, and some things could probably have been designed more efficiently an elegantly. Moreover, as english is not my native language, you will find many faults and inaccuracies through this text. Please excuse me in advance.

In any case, don't hesitate to send me your feedbacks at the following address :

julien (at) nozav (dot) org

Application installation and configuration

The first thing we have to do is to install and setup the application. If you already had done this before, you could probably skip this section.

Requirements

In this tutorial you are supposed to have a recent Rails version installed (at least version 2.0) and a functional database system. Here we will use sqlite, but you can replace it by the one you prefer without any problem.

Files

First, we have to create the "skeleton" of our application in the directory of our choice1 :

$ rails ajaxtable
$ cd ajaxtable

As for the 2.0 version of Rails, the pagination features have been moved from Rails core into a plugin called classic pagination. Unfortunately many people prefer to use another plugin called will_paginate, but I didn't manage to make this tutorial work with this plugin. So we have to install the classic pagination plugin with the following command :

$ ruby script/plugin install svn://errtheblog.com/svn/plugins/classic_pagination

As it is a very basic application, we will use only one model, which will represent table items, and one controller for these items. We will generate the corresponding files with the Rails scripts :

$ ruby script/generate model Item
$ ruby script/generate controller Item

Database

Once we have the files, we have to setup a data source. For simplicity here we will use sqlite in conjunction with Rails schema. This means that we will describe our database inside Rails and let it manage database creation.

First, we have to update the development data source in config/database.yml :

development:
  adapter: sqlite3
  database: db/development.db

Next, we will create the database by using Rails migration tools.

$ ruby script/generate migration database_creation

This should have created a db/migrate/001_database_creation.rb file in which we will define our database schema the following way :

class DatabaseCreation < ActiveRecord::Migration

  def self.up
    create_table :items do |t|
      t.column :name, :string, :limit => 30
      t.column :quantity, :integer, :null => false, :default => 0
      t.column :price,  :integer, :null => false, :default => 0
    end
  end

  def self.down
    drop_table :items
  end

end

This just defines one table named items with three columns2 : a string field called name, and two integer columns called quantity and price (nothing really original, yes...).

Then, we just have to do a :

$ rake db:migrate

And you should now have a development.db file in your db directory which is your sqlite database created by Rails with the items table inside.

Then you can insert some items in your table to have something to look at while developing your application. You can make it by hand or by saving the following SQL instructions in a db/dump.sql file3 :

BEGIN TRANSACTION;
INSERT INTO "items" VALUES(1, 'hoe', 3, 10);
INSERT INTO "items" VALUES(2, 'wheelbarrow', 2, 60);
INSERT INTO "items" VALUES(3, 'gherkin', 15, 3);
INSERT INTO "items" VALUES(4, 'batman', 1, 3000);
INSERT INTO "items" VALUES(5, 'fish sausage', 2, 8);
INSERT INTO "items" VALUES(6, 'sauerkraut', 9, 9);
INSERT INTO "items" VALUES(7, 'watering-can', 4, 13);
INSERT INTO "items" VALUES(8, 'dandelions', 78, 1);
INSERT INTO "items" VALUES(9, 'refrigerator', 12, 250);
INSERT INTO "items" VALUES(10, 'flying matches', 8, 145);
INSERT INTO "items" VALUES(11, 'broken accordion', 1, 18);
INSERT INTO "items" VALUES(12, 'savage whisper', 5, 7);
INSERT INTO "items" VALUES(13, 'hysterical snail', 8, 13);
COMMIT;

And then doing a :

$ sqlite3 db/development.db < db/dump.sql

Creating the model

As you probably know, a Rails application is divided into three main types of components : models, views and controllers. We will create them one by one.

The model of our application will be very simple here. In fact, as we don't have any complex query to send to the database, it will stay the same as generated by Rails in our application installation, ie empty. So we will not touch the app/models/item.rb file.

Well, I hope this step has not been too difficult !

Creating the view

The view of our application will be split into two components : a layout, a view and a partial.

Layout

The layout is a page template which will be used to render several views. It contains some non-varying elements such as HTML header and footer, menus, design elements, etc. The utility of a layout is very limited in our example, as we will have only one page. But it is a tutorial, after all...

Here, the layout will be located in app/views/layouts/item.rhtml, and will contain something like :

<html>
<head>
  <title>Ajax table manipulation attempt</title>
  <%= stylesheet_link_tag "style" %>
  <%= javascript_include_tag :defaults %>
</head>
<body>

<div id="content">
<%= @content_for_layout %>
</div>

</body>
</html>

An important thing here is the javascript_include_tag tag which will be replaced by the javascript libraries used by Rails to provide Ajax features.

Also note the @content_for_layout instruction, which will be replaced by the generated content when needed.

View

The view component will be used to render a particular action of our controller, which we will describe immediately after this. As it is a list action of our item controller, the view will be located in app/views/item/list.rhtml.

The content of the file will be the following :

<h1>Welcome to my wonderful items list</h1>

<p>This list is updated real-time from the world largest database of
items by using bleeding-edge technology associated to full-featured
Web 2.0 eye candy goodies.</p>

<p>But be careful, there are plenty of bugs.</p>

<h2>And here is the list...</h2>

<p>
<form name="sform" action="" style="display:inline;">
<label for="item_name">Filter on item name  : </label>
<%= text_field_tag("query", params['query'], :size => 10 ) %>
</form>

<%= image_tag("spinner.gif",
              :align => "absmiddle",
              :border => 0,
              :id => "spinner",
              :style =>"display: none;" ) %>
</p>

<%= observe_field 'query',  :frequency => 2,
         :update => 'table',
         :before => "Element.show('spinner')",
         :success => "Element.hide('spinner')",
         :url => {:action => 'list'},
         :with => 'query' %>

<div id="table">
<%= render :partial => "items_list" %>
</div>

There is nothing really complicated in the beginning. A stupid introduction text and a search form which will be used to filter items on their names.

Then, we have an image element whose id is spinner and which is hidden by default. This image will be shown briefly during Ajax operations on the page, and then hidden again when everything is finished. You can get some public domain images here :

http://mentalized.net/activity-indicators/

You have to put the chosen image file in the public/images directory of your application.

The following observe_field instruction is a bit more unusual. What this instruction does here is to add a new Ajax observer on the query field. This observer will periodically (here, every 2 seconds, look at the frequency parameter) check the content of this field and will react if this content has changed.

The action to be taken is described by the remaining parameters :

The concrete effect of all this stuff is quite simple. When the user type something in the query field, the observer will detect the new content of the field, and then generate an Ajax request sent to the server with the url and with parameters. As soon as the request is sent, the before action toggles the visibility of the spinner XHTML element, and the user can see the image. When the request answer his received, the table XHTML element is updated, and the success action hides the spinner image again.

Just for information, here is what the observe_field function returns with the given parameters :

<script type="text/javascript">
//<![CDATA[
new Form.Element.Observer('query', 2, function(element, value) {Element.show('spinner'); new Ajax.Updater('table', '/item/list', {asynchronous:true, evalScripts:true, onSuccess:function(request){Element.hide('spinner')}, parameters:'query=' + value})})
//]]>
</script>

We spent some time here to detail the different options because we will meet them again in nearly all the other Ajax methods in this tutorial.

And, to finish this view description, we have a call to a partial element called items_list. We will describe this concept and its content in detail after setting up our controller.

Creating the controller

The controller will handle different kinds of requests in order to update the view by calling the model depending on the request type and its parameters.

Our Item controller will be very simple here, and will only include one action called list. We will not implement any other CRUD (Create, read, update, delete) action in this tutorial.

So here is the content of app/controllers/item_controller.rb :

class ItemController < ApplicationController

  def list

    items_per_page = 10

    sort = case params['sort']
           when "name"  then "name"
           when "qty"   then "quantity"
           when "price" then "price"
           when "name_reverse"  then "name DESC"
           when "qty_reverse"   then "quantity DESC"
           when "price_reverse" then "price DESC"
           end

    conditions = ["name LIKE ?", "%#{params[:query]}%"] unless params[:query].nil?

    @total = Item.count(:conditions => conditions)
    @items_pages, @items = paginate :items, :order => sort, :conditions => conditions, :per_page => items_per_page

    if request.xml_http_request?
      render :partial => "items_list", :layout => false
    end

  end

end

Let's explain this a bit.

Our controller only defines one action, called list. This action will have to handle every request sent to our application.

First, we define a variable called items_per_page which will represent the number of lines our table will show on each page.

Next, we define another variable called sort, depending on the request parameter of the same name. The case can be used to mask the real fields name in our database, which can be more suitable for security reasons. The reverse string in the sort parameter indicates that sorting should be made in descending order.

Then, a conditions variable is constructed if a query request parameter is present. It is an SQL-like instruction which will be used to filter our database query results based on the content of the name field.

After that, we assign the total number of items in our database matching the conditions to the @total variable.

And eventually, we find a call to the Rails paginate instruction. We give to this instruction the model it should be associated to (:items), a sort field, conditions to be applied to the query, and the number of items we want on each page. And it automagically returns us a paginator object called @items_pages and the items for the current page number (this page number is passed as a page request parameter, which is transparent here). The paginator object will be used later to display pagination links.

All we have seen for the controller until now applied to every request passed to the application, whatever its type. The most used kinds of HTTP requests are the traditional GET and POST, but Rails and Ajax can make an important use of a third type, whose name is XmlHttpRequest4.

This kind of request is launched by javascript, which will send it in the background via HTTP to the server. One use of the kind of request is to get a fragment of XHTML page used to update a part of the browser display, without reloading the entire page. This can give the user the impression of a fastest and more responsive interface.

That is what the final part of our controller deals with : it tests if the request it has received is of type xml_http_request5. In this case, it will not render the entire list view, but only a fragment of it, the now famous partial whose name is items_list.

So, as we can see, this xml_http_request test is the only "really Ajax" thing we met in the controller. That is because Ajax things are linked to the interface of our application, ie the view, and more specifically the part of it which will be managed with Ajax, ie the partial.

So, could you guess what we are going to do now ?

Creating the partial

A partial view component is used to render only a part of a page. It is very useful to separate some repeated view constructions in order to reuse them and to follow the Don't Repeat Yourself (DRY) Rails principle. But it is also really useful with Ajax.

Indeed, the effect of our Ajax actions in this tutorial will always be to redisplay the table of items, whereas it is to change the page, the order or the search. So we will have to refresh the table, but not the entire page, otherwise an Ajax call is of very limited utility. That's why we will separate all the elements which will have to be updated in a partial file6.

Partial filenames always begin with an underscore, so here our partial will be in app/views/item/_items_list.rhtml.

Its content will be the following :


<% if @total == 0 %>

<p>No items found...</p>

<% else %>

<p>Number of items found : <b><%= @total %></b></p>

<p>
<% if @items_pages.page_count > 1 %>
Page&nbsp;:
<%= pagination_links_remote @items_pages %>
<% end %>
</p>


<table>
  <thead>
    <tr>
      <td <%= sort_td_class_helper "name" %>>
        <%= sort_link_helper "Name", "name" %>
      </td>
      <td <%= sort_td_class_helper "qty" %>>
        <%= sort_link_helper "Quantity", "qty" %>
      </td>
      <td <%= sort_td_class_helper "price" %>>
        <%= sort_link_helper "Price", "price" %>
      </td>
    </tr>
  </thead>
  <tbody>
    <% @items.each do |i| %>
    <tr class="<%= cycle("even","odd") %>">
      <td><%= i.name %></td>
      <td><%= i.quantity %></td>
      <td><%= i.price %></td>
    </tr>
    <% end %>
  </tbody>
</table>

<% end %>

Some explanations may be needed here. The partial contains the table pagination and sorting management. We will see both in a more detailed way.

Pagination helpers

In the beginning, we have a test to see if the total number of items found is greater than zero. In this case, we display this total number and then a paragraph which will be empty if there is only one page in our items pagination.

If we have more than one page of results, we clearly have to display pagination links. Rails already have methods to help dealing with them, but we will have to customize them a little. For this, we will create a helper.

A helper is a Ruby function which is used to help generating the view. The aim is to separate the code in these functions from the view itself, and to make this code reusable.

Our helpers will all be located in app/helpers/item_helper.rb. Each method in this file will be accessible from our view. If we wanted them to be usable by every view of our application, we could have put them in application_helper.rb.

Well, enough blabla, here is the code of our pagination_links_remote helper :

def pagination_links_remote(paginator)
  page_options = {:window_size => 1}
  pagination_links_each(paginator, page_options) do |n|
    options = {
      :url => {:action => 'list', :params => params.merge({:page => n})},
      :update => 'table',
      :before => "Element.show('spinner')",
      :success => "Element.hide('spinner')"
    }
    html_options = {:href => url_for(:action => 'list', :params => params.merge({:page => n}))}
    link_to_remote(n.to_s, options, html_options)
  end
end

This method takes a paginator object as argument. This is a Rails object which keeps information about the current pagination state (number of pages, current page, etc.).

We then define a page_options hash which contains only one item called window_size. This is a parameter which tells Rails how many pages it has to show around the current one. For example, if window_size equals one, we will have something like :

1 ... 5 6 7 ... 13

If window_size equals 2 :

1 ... 4 5 6 7 8 ... 13

We could then make a call to the pagination_links function, which would generate the correct XHTML code for our links. But the problem is that it will generate "classic" XHTML links, but not the Ajax one. So we will have to redefine the links ourselves. This is done with the pagination_links_each method.

This method iterates over the pages to be displayed and then applies the block passed as its argument. Our block first defines two kinds of options :

Then, there is just a call to the link_to_remote function, which will generate the complete XHTML for our link, including the javascript and the classic XHTML href part.

For example, here is what the helper returns if there are two pages, the first one being currently displayed :

1 <a href="/item/list?page=2" onclick="Element.show('spinner'); new Ajax.Updater('table', '/item/list?page=2', {asynchronous:true, evalScripts:true, onSuccess:function(request){Element.hide('spinner')}}); return false;">2</a>

Sorting helpers

Back to our partial. After the pagination links we start to define the table by itself. The definition of the table header is a bit complicated, because that is where we define the links to sort our data by a column or another. Each header cell makes use of two helpers.

The first helper is nothing but necessary. It is called sort_td_class_helper, and his only goal is to add a class="sortup" if the column is currently the one used to sort the table, or a class="sortdown" if it is used to sort in reverse order. The only utility is to allow, with CSS, to indicates to the user which column is currently used as a sorting field.

The code is nothing interesting :

def sort_td_class_helper(param)
  result = 'class="sortup"' if params[:sort] == param
  result = 'class="sortdown"' if params[:sort] == param + "_reverse"
  return result
end

Then, we have a second helper, called sort_link_helper.

def sort_link_helper(text, param)
  key = param
  key += "_reverse" if params[:sort] == param
  options = {
      :url => {:action => 'list', :params => params.merge({:sort => key, :page => nil})},
      :update => 'table',
      :before => "Element.show('spinner')",
      :success => "Element.hide('spinner')"
  }
  html_options = {
    :title => "Sort by this field",
    :href => url_for(:action => 'list', :params => params.merge({:sort => key, :page => nil}))
  }
  link_to_remote(text, options, html_options)
end

This helper is in fact very similar to the pagination_links_remote one, seen above. It takes two arguments :

The first two lines define a new variable, called key, which gets the value of the param argument, ie the sort key. The string _reverse is concatenated to it if param is already the sorting key. This is used to implement sorting in both ascending and descending order. If the user select a sort link, it will sort by ascending order ; if it selects the same link again, it will sort by descending order, and so on. You can look at the controller if you don't find these explanations clear enough.

The rest of the helper is to define the options for the final call to the link_to_remote function. They are very similar to those of pagination_links_remote :

Here is what the sort_link_helper returns for a call with the strings "Quantity" and "qty" as text and param arguments :

<a href="/item/list?sort=qty" onclick="Element.show('spinner'); new Ajax.Updater('table', '/item/list?sort=qty', {asynchronous:true, evalScripts:true, onSuccess:function(request){Element.hide('spinner')}}); return false;" title="Sort by this field">Quantity</a>

Table body

The end of the partial just displayed our table body, with one item on each line. The only thing which may be noted here is the use of the cycle Rails function, which will automatically and alternatively add a "odd" or "even" style class to our table rows, which can be very useful to display it in a more visible way.

That's all folks !

We have now seen every pieces of our application more or less in detail. In theory you could see the result by starting the WebRick server and point your browser to :

http://localhost:3000/item/list

I hope this document could have been useful to you. I repeat that you can send me any feedback at the mail address given in the introduction.

About this document

This document is published under a Creative Commons Attribution licence.

Thanks to Nicolas St-Laurent, Rachel McConnell and Michel Loiseleur for their feedbacks.

Thanks to Juan Lupión for the spanish translation of this tutorial.

1. These instructions are shown in a linux command line environment, as it is mine.

2. In fact, our table has four columns, as an id field is automatically added by Rails.

3. If you use MySQL as database, the correct syntax is INSERT INTO items VALUES (1, 'hoe', 3, 10);

4. In fact, the XmlHttpRequest is not really another HTTP request type. It is a traditional POST or GET request sent and treated by javascript on client side asynchronously.

5. The request.xml_http_request? instruction can be abbreviated in request.xhr?

6. Note that there could have been other ways to do it. In particular we could have created another action (ajax_list for example) to deal with xml_http_request requests, with an associated view but without any partial. One problem of this method here would have been to duplicate a great part of the code between the list and ajax_list actions. So, TIMTOWTDI, but DRY.