#228 Sortable Table Columns

In this episode we’re going to show you how to make a table sortable by clicking on one of its columns. Below is a page from a store application that shows a list of products in a table. We want to be able to sort the items in the table by clicking on one of the table headers. This might not be particularly useful here where we only have a few products but if we had hundreds of items in a paginated table then being able to sort the columns would be very helpful indeed.

There are a number of plugins available that we could use here such as Searchlogic, which was covered in episode 176 [watch, read], but we’re not going to use any of them here, instead doing it all from scratch.

Making The Links

We’ll start in the view code for the index action which contains the table that renders the products.

<% title "Products" %>

<table class="pretty">
  <tr>
    <th>Name</th>
    <th>Price</th>
    <th>Released</th>
  </tr>
  <% for product in @products %>
  <tr>
    <td><%= product.name %></td>
    <td class="price"><%= number_to_currency(product.price, :unit => "&pound;") %></td>
    <td><%= product.released_at.strftime("%B %e, %Y") %></td>
  </tr>
  <% end %>
</table>

<p><%= link_to "New Product", new_product_path %></p>

Most of the changes we need to make will be to the header cells in this table. We want the text in those cells to be links so that the table is sorted when they are clicked. We’ll approach this in small steps doing the simplest thing that works at each stage. This way we’ll see the process evolve as we progress.

The first thing we’ll do is make the table headings links, so we’ll need to add link_to before each header cell’s text. (We can do this more quickly in TextMate by holding down the option key and selecting the three columns at the end of the opening <th> tag. Any text we type then will be added to all three lines.) The page we want each link to go to is the same page we’re on but with different query string parameters. To do that we just need to specify a hash as the second parameter to link_to.

<th><%= link_to "Name", :sort => "name" %></th>
<th><%= link_to "Price", :sort => "price" %></th>
<th><%= link_to "Released", :sort => "released_at" %></th>

When we reload the products page the header cells will have turned to links and if we hover the cursor over each one we’ll see the appropriate parameter in the query string of the link’s URL.

Sorting The Products

To get the sorting working we need to modify the ProductController’s index action so that it orders the products according to the sort parameter in the query string.

def index
  @products = Product.order(params[:sort])
end

We’re using the Rails 3 order method here to sort the products, but if this was a Rails 2 application we could use find along with a hash to specify the order. Note that we’re passing in user parameters directly into the order clause and this is something we shouldn’t do as the input isn’t sanitized and there’s a danger of SQL injection. (This topic was covered back in episode 25 [watch, read].) We’ll leave it as it is for now and come back later in this episode to fix it.

With this code in place the sorting will now work and when we reload the page and click on one of the headings the table will be sorted by the field we clicked.

Toggling The Sort Order

We’ve made quite a lot of progress so far without having to write much code but there are still other features to add, such as ordering a column in the reverse direction by clicking it again, and showing an arrow icon to indicate the current sort.

We’ll tackle toggling first. When we click the link for the column the table’s currently sorted by it should show the table sorted by the same field but in the opposite order. Doing this in the view code would add a lot of logic there and create duplication so we’ll create a helper method to generate each link.

We’ll call the new helper method will be called sortable and it will take two arguments, the second of which is optional. The first argument will be the column name and the second will be the column header text if this is different from the column name. Our table header will now look like this:

<tr>
  <th><%= sortable "name" %></th>
  <th><%= sortable "price" %></th>
  <th><%= sortable "released_at", "Released" %></th>
</tr>

We’ll write the sortable method in the ApplicationHelper module.

module ApplicationHelper
  def sortable(column, title = nil)
    title ||= column.titleize
    direction = (column == params[:sort] && params[:direction] == "asc") ? "desc" : "asc"
    link_to title, :sort => column, :direction => direction
  end
end

The method has the two arguments we mentioned earlier, with the title argument having a default value of nil so that we can set a value based on the column argument if only one argument is passed in. Next we have the logic that determines what the sort direction for the link. If the column we’re generating the link for is the current sort column and the current direction is ascending then we’ll set the direction to desc so that the next time that field is clicked the column is sorted the other way. In all other cases we want the sort direction to be ascending. Having worked out the direction we can add it as a parameter to the link.

If we reload the page and click the “Name” link the table will be sorted by name in ascending order. When we click the “Name” link again the direction parameter changes to desc in the query string but the table isn’t sorted in descending order.

The table isn’t being re-sorted in the correct direction because we’re not taking the direction into consideration in the controller. To fix this we just need to add the direction into the parameter that passed to the order method when we fetch all of the products in the ProductController’s index action.

def index
  @products = Product.order(params[:sort] + ' ' + params[:direction])
end

Again we’re passing parameters from the user straight into a query here which is unsafe, but we will come back to this later and correct it. When we reload the page, however, the items are sorted correctly and we can now click on any of the columns to sort by that column in either ascending or descending order.

Adding Default Values

While out table now seems to be working well but if we remove the query string from the URL and try going to the products page we’ll get an error as the code in the controller will try to read the parameter values from the query string. As these parameters are both now nil the error is thrown when the code tries to join them together as a string.

We need to set some default values for the sort and direction parameters. We could modify the params hash directly and set both values if they don’t exist in the query string but instead we’ll write two methods in the controller that will return the parameter or a default value. We’ll then use those methods to build up the order argument.

class ProductsController < ApplicationController
  helper_method :sort_column, :sort_direction
  def index
    @products = Product.order(sort_column + ' ' + sort_direction)
  end
  
  private
  def sort_column
    params[:sort] || "name"
  end
  
  def sort_direction
    params[:direction] || "asc"
  end
end

We need to make these two methods available to the ApplicationHelper so that they can be used in the sortable method we wrote earlier and so we’ve made them both helper methods. Next we’ll modify sortable to make use of these methods.

module ApplicationHelper
  def sortable(column, title = nil)
    title ||= column.titleize
    direction = (column == sort_column && sort_direction == "asc") ? "desc" : "asc"
    link_to title, :sort => column, :direction => direction
  end
end

Now if we visit the products page without specifying any parameters the page will use the default of sorting by the product’s name in ascending order. When we click the “Name” link for the first time it will re-sort the list by descending order of name, as we’d expect.

Securing The Query

As we mentioned earlier it’s not a good idea to pass user input directly into a database query, such as the order clause, because of the dangers of SQL injection. We need to sanitize the input before passing it to the order clause. We could do some generic sanitizing on the parameters but instead we’ll take a stricter approach to what we allow.

As we’ve written accessor methods for the sort column and direction we can add code in these to ensure that the values passed to order are valid and safe. The sort direction is only going to have one of two values so we can check that the passed parameter matches one of those and, if not, set the default value of asc.

def sort_direction
  %w[asc desc].include?(params[:direction]) ?  params[:direction] : "asc"
end

We can do something similar in the sort_column method to make sure that the sort parameter matches one of the fields in the Product model, defaulting to “name” otherwise.

def sort_column
  Product.column_names.include?(params[:sort]) ? params[:sort] : "name"
end

You might want to be even more strict here and restrict the sorting to only certain columns in the table, but this approach will work well enough here.

Indicating the Current Sort Field

We’ll finish off this episode by adding an an icon next to the current sort field that indicates the direction in which it is being sorted. We can do this with CSS but we’ll first need to adjust our sortable helper method so that it adds a class name to the link in the header cell for the current sort field.

module ApplicationHelper
  def sortable(column, title = nil)
    title ||= column.titleize
    css_class = (column == sort_column) ? "current #{sort_direction}" : nil
    direction = (column == sort_column && sort_direction == "asc") ? "desc" : "asc"
    link_to title, {:sort => column, :direction => direction}, {:class => css_class}
  end
end

In the method we’ve now added a css_class variable. If the current column is the sort column that variable has a value of either “current asc” or “current desc” depending on the current sort direction, otherwise it it set to nil.

In the code that generates the link we then add a class attribute. So that the attribute isn’t added to the query string we need to separate the parameter into two hashes. This way the class attribute will be added as an attribute to the anchor tag.

We’ve already added the two image files we’re going to use into our application’s /public/images directory so we just need to add some CSS to our application’s stylesheet so that the right image is shown.

.pretty th .current {
  padding-right: 12px;
  background-repeat: no-repeat;
  background-position: right center;
}
.pretty th .asc { 
  background-image: url(/images/up_arrow.gif); 
}

.pretty th .desc { 
  background-image: url(/images/down_arrow.gif); 
}

When we re-sort the table now the column that the table is currently sorted by has an arrow indicating that it is the current sort column.

That’s it for this episode. We now have what we want: a table that we can sort by any of its columns and with an indicator to show us what the current sort column is.