#228 Sortable Table Columns

Very interesting episode, I just wondering how good is to write code in one line which do so much things (and where is the limit!)... it is consider a good practice?

Hope to see more railscasts like that! :)

Great cast, as usual! For those folks looking for a plugin that does this and more for Rails 3, I'd like to add a suggestion to try out MetaSearch.

Alternately, if you want to maintain search (or other GET params), be sure to include them in your call to link_to in the sortable method.

This is one of your best episodes!
Thank you very much.
Even if it is for beginners in a way, it is nice to see your logic and way of doing things.

Wow, it's freaky how you seem to always release casts right when I'm trying to figure something out. Just sat down yesterday and added sortable columns to my wish list.

Amazing.

Thanks for all your hard work.

Awesome, however, what if the column you want to search on is a computed column?

Say, its product.release_code

where:

def release_code
  "#{name.slice(0,2).upcase}-#{released_at..strftime("%Y-%m-%dT%H:%M:%S")}"
end

How would you go about sorting on this column on the table as its not a column in the database?

Would you first check to see if the column exists on the table and if not do an array sort?

Thanks,
~ Tom

@Tom: you can implement what you want by modifying the 'sort_column' method to return an equivalent sql method that will be used by your database to order the fetched records.

I think it's much better to let JS do it, this would be better for the server, and a way more faster for the user.

Wouldn't you need to escape the params in the order clause to prevent sql injection, or is Rails 3 doing this automatically now?

In the private methods in the controller I would have used a regexp like so:

    params[:sort][/(asc|desc)/] || "asc"

Is it a bad idea?

@Jim, that was kind of a bad example, what if it was something that could not be replicated in SQL? If it was a method that did alot more than just a concat? How would you implement this then?

~ Tom

For use with Rails 3, I would like to recommend Ernie Miller's meta_search gem. Its amazingly simple and yet so sophisticated. I really prefer it more than searchlogic lately.

http://github.com/ernie/meta_search

Another great episode. Please don't apologize for doing a "basic" show. It's refreshing!

I did a similar implementation, but used HTML entities for the arrows ▲ and ▼.

Also I didn't make private functions in the controller but instead used "unless yadda-yadda" in the index method. I can see how this might be DRYer here because you use those functions in the helper too (I didn't).

@CodeOfficer: thanks for the plug!

@Tom: If your sort can't be implemented at the database level, then you should probably first examine 2 things:

1. Your database schema. It should support your most common use cases well. Do you need a cache column for your sortable data (say, comments_count, or something) or is there something else?
2. Is it absolutely necessary to sort on the column you're trying to add? If you're unable to do the sort in the database, you're going to have to load up the entire set of all records to do it in Ruby code, even if you're paginating (unless you only want your sort column to sort the current page of results).

Assuming you're still sure you want to do it, then you would most likely want to create another method that you call in your index action, which will use Ruby's Enumerable#sort to do the trick.

@Tom, maybe just get the @products and then ruby sort them anyway you like. It worked for me on a small project -- I didn't need to check speed or mem perf for that. You should though. If you chose this route, you should probably put that function into the model. Need more?

Great screencast, I just implemented the same thing, however I used CSS triangles to show the sort order in combination with the :after pseudo element. Unfortunately :after doesn't work in IE6/7 natively, but it saves a HTTP request for the modern browsers.

I've seen pure Javascript code that does the same rather then using HTTP GET.

I wonder if there is a good Ruby to XSLT translator instead of working hard in XSLT at the first place ...

Anyway, thanks for another great video cast on Rails, as always I learn a lot from your casts.

@Omar: But what if you have so many results that you have to use a pager? You don't have all the data in the browser to re-sort, so you have to ask the server to do it.

Great Episode! I am a noob to rails and your railscasts have got me excited for ruby on rails. I am just starting out, but have watched almost half of your videos in the past week. I am starting a few new projects and going to dive right in trying to use it.

I have been reading about keeping things DRY in Rails and just wondering about the two helper methods. sort_column and sort_direction. With multiple models, I would have to specify those functions in every model that needs to be sorted. Is this the way to do it, or would you extract those functions out into more global help methods and then call them some other way.

If that is out of scope for the video i understand. i am just trying to wrap my head around best practices.

thanks again!

May I suggest using http://www.kryogenix.org/code/browser/sorttable/

100% javascript solution that runs in the browser without the need to access the server.

You can use helper methods to keep the views clean too.

The only inconvenient is when using pagination (which is not the case in this episode).

Hi, great episode! I was just trying to implement this on my site (Rails 2.3.8), but I'm confused. What does the "order hash" described as the Rails 2 alternative to the Rails 3 Product.order(params[:sort]), look like?

Thanks!

Hello Ryan,

The video doesnt work after a certain point.

Is there any problem?

Thanks

Dakshata

Nice episode, very clean and informative.
    
For those interested, there's a gem called handles_sortable_columns which gives you this exact functionality with just 2 lines added to your controller. Very simple, yet configurable and fully documented.

http://github.com/dadooda/handles_sortable_columns

Ryan, thanks for encouraging me to publish the stuff I've been putting off for a few months already. :)

Ryan, I always manage to learn something totally useful from every episode! I've done sortable columns a million times, but the cmd + option + a text mate trick is a life saver. Thanks and great as always!

@Dakshata:
I have problems when using VLC and playing the video from the network.
 Downloading it first, then playing the downloaded file works fine however.

@"Confused from Rails 2.3.3":

Rails 3 syntax:
Product.order(sort_column + " " + sort_direction)

"plain old" Rails 2.3 syntax:
Product.all(:order => sort_column + " " + sort_direction)

Just a little style note:
def sortable(column, title = nil)
  title ||= column.titleize

#=>
def sortable(column, title = column.titleize)

Great episode

@Benoit Daloze

Try calling your method like this:

sortable("some_col", nil) # title will be nil instead of the default value

That's not exactly the same behaviour.

Best table sorter solution I've tried: http://tablesorter.com/docs It's all js works with pages and has tons of options. It's also really easy to get running.

Another great episode Ryan.....I have started to re-write an opensource crm program that was done in php/mysql and I have since made the leap to re-write the whole CRM program from the ground up using Ruby/Rails.
I have all the episodes on my ipod so when I get stuck or need help remembering some finer points, I can quickly find then.
From one Opensource and Free provider to another I'll be making a donation now..
Keep up the great work Ryan..

Great and easy to implement but I'm running into a problem. How would add sorting to a table that contains data from a linked table?

Example: I have a Product model that has an assigned Product Type. Currently I get an error when trying to add sorting on the Product.ProductType.Name column.

Any thoughts?

I really prefer it more than searchlogic lately.

You may want to change the sort_column fuction to

    params[:sort] ||= "opp"

if you are finding that you cannot search on a column because you are running e.g. Post.includes(:categories)

I had no idea about the cmd-opt-a hotkey. thank you so much!

undefined local variable or method `sort_column' for #<#<Class:0x236d5e8>:0x236b920>

for the line:
<th><%= sortable "reference", "Reference" %></th>

What now? (rails 3.0)

I copied/pasted your code in my controller, nothing more, nothing less.

When i comment all the helper code after:
title ||= column.titleize

The page displays correctly (just without sorting)

Any help is welcome.

I am to a great extent impressed with the article I have just read interesting very good.I read the Blog Nice site I found and I bookmarked the site… Plan on coming back later to spend a little time there.

Thanks Ryan for the great tutorials. My question is the same as comment #42, is it possible to sort on a column in a linked table? If so, would you explain how this could be done?

Thanks!

undefined local variable or method `sort_column' for #<#<Class:0x236d5e8>:0x236b920>

with Rails 2.3.11.

Any help is welcome.

Any suggestions on how to get UTF-8 characters like "

What if you wanted to order by using ajax?

Hey guys, I modified this a little bit so that it could be used with Twitter's bootstrap framework. It allows for the TH tag to be clickable instead of making the text a link, check it out!

https://gist.github.com/1392144

Guys anyone knows how to apply this sortable logic for associations.

For example, i have a table videos (id, name, video_category_id) and video_categories (id, name). I am displaying video name and video category name in a table. Now when someone wants to sort using video category name, it should sort records with video category name instead of using video category id from the association column in video table.

I have the same problem as jayeshmori. Did you find a solution?

jayeshmori, Ben,

My way to solve that issue:

Use the column name with a table alias, for example: 'us.name'.
In the controller, always retrieve info joining the other table, with its alias 'Product.joins('JOIN table alias ON field = field')'

And then use as the column name, the alias + the column name, as I stated before.

And remember to include this 'special' name in the valid column names in the sort_column private method.

Hope it helps!

Hi guys, I have an app listing a bunch of inventory items and on my views I am showing columns with item.created_at and item.updated_at. After following this tutorial I have all of my columns sorting fine with the "arrows", except that I can't get the item.created_at and item.updated_at columns to sort. Any tips?

Thanks for any help!!!

Luis Pablo or anybody else:
Could you please post some example code in more detail. I´m new to Rails and don´t get it working. Don´t know in which controller to write the join and so on :-(

Maybe someone can post the exact steps for jayeshmori´s example with the video and category?

Hope someone can help!

For using this with associations I found the following worked for me (I am displaying a list of transactions, the association is a category). In my Transactions controller:

  def index
    if params[:sort] == "category"
      @transactions = sort_by_category
    else
      @transactions = sort
    end
  end
  
  private
  
    def sort
      current_user.transactions.order(sort_column + " " + sort_direction).page(params[:page])
    end
    
    def sort_by_category
      current_user.transactions.paginate(
            :page => params[:page],
            :include => :category,
            :order => "categories.name #{sort_direction}")
    end
  
    def sort_column
      Transaction.column_names.include?(params[:sort]) ? params[:sort] : "date"
    end
  
    def sort_direction
      %w[asc desc].include?(params[:direction]) ? params[:direction] : "desc"
    end

I'm paging the results with will_paginate but if not paging something along the lines of the following would work I believe:

current_user.transactions.find(:all, :include => :category, :order => "categories.name #{sort_direction}")

Thx Nico, that helped!

Very helpful, thanks!
If you want to use CSS triangles instead of images:

th .asc:after {
  width: 0;
  height: 0;
  content: "";
  border-left: 5px solid transparent;
  border-right: 5px solid transparent;
  border-top: 5px solid #333;
  position: absolute;
  margin-top: 7px;
  margin-left: 5px;
}

th .desc:after {
  width: 0;
  height: 0;
  content: "";
  border-left: 5px solid transparent;
  border-right: 5px solid transparent;
  border-bottom: 5px solid #333;
  position: absolute;
  margin-top: 7px;
  margin-left: 5px;
}

great! I just made a minor adjustment so that it adapts the twitter bootstrap framework's icons

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} <i class='#{direction == "desc" ? "icon-chevron-down" : "icon-chevron-up"}'></i>".html_safe, {:sort => column, :direction => direction}, {:class => css_class}
end

Is there a way to set the default direction of certain columns to desc?

For example Popularity that uses the amount of hits. Right now the least popular item is when you click it once...

Thanks in advance!

Never mind I figured it out. I just added this helper function:

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

I want to implement this in MongoDB database. Any idea? If some hints then welcome. Also i asked this question in stackoverflow.
http://stackoverflow.com/questions/14090621/sortable-table-columns-is-not-working-working-in-mongodb

I'm running into an issue with Rails (v4) images (ex: the arrows) If I put it into assets/images/
do I have to do precompiling? The asset pipeline is really confusing me

Thanks a lot, this was very helpful. Just got the last part with arrows to work, looks great! The only problem I had is that when I write in css a .current .asc{} or th .current .asc{} it doesn't work, I used th .asc{} and th .desc{} which worked. (Not sure why it has problem stacking two classes.)
Also for images url is: url(/assets/arrow_up.png); So basically had to replace "images" with "assets" and it works.

You're my dude Bates

This was an excellent lesson, which I implemented immediately. I had some trouble with the .css file, probably because it was a little weird to start. I had to delete the '.prettify' stuff. Here is what worked for me.

th a.current {
padding-right: 20px;
background-repeat: no-repeat;
background-position: right center;
}

th a.current.asc {
background-image: url(images/up_arrow.gif);
}

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

For those trying, if have not already, to sort by association or be more specific with which columns can be sorted;

  def sort_column
    acceptable_cols = ["users.name", "users.email", "addresses.city", "addresses.state"]
    acceptable_cols.include?(params[:sort]) ? params[:sort] : "name"
  end

Just specifying an array of the allowed columns, rather than relying on the columns defined by the model allows you better control

I don't know if this is the best solution, I'm about two weeks new to Ruby and RoR.

I am impressed with the way you covers the whole article. Thanks for sharing and keep posting such nice information. I am truly pleased to read this website which defines our thoughts. looking more from you in future. I think you did an outstanding job ! Thanks for this!

This works as is on Rails 4.2 except for the CSS. Combining other people's advice above the below works for me. Remove .pretty and add a. Change the url from the image folder to the assets folder.

th a.current {
  padding-right: 20px;
  background-repeat: no-repeat;
  background-position: right center;
}
th a.current.asc {
  background-image: url(/assets/up_arrow.gif);
}
th a.current.desc {
  background-image: url(/assets/down_arrow.gif);
}

You can also use Bootstrap 3 glyphicons instead of background images. Replace the helper method with this and leave out the css classes.

def sortable(column, title = nil)
  title ||= column.titleize
  if column != sort_column
    css_class = nil
  elsif sort_direction == "asc"
    css_class = "glyphicon glyphicon-triangle-top"
  else
    css_class = "glyphicon glyphicon-triangle-bottom"
  end
  direction = column == sort_column && sort_direction == "asc" ? "desc" : "asc"
  link_to "#{title} <span class='#{css_class}'></span>".html_safe, {:sort => column, :direction => direction}
end

How can I pass an additions parameter through the clickable tble column link ?

Using FontAwesome as chevron

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} #{content_tag(:i, '', :class => "fa fa-chevron-#{direction == 'asc' ? 'up': 'down'}", :style => "font-size: 6px;") if css_class.present?}").html_safe), params.merge(:sort => column, :direction => direction, :page => nil), {:class => css_class}
end