Garbage Burrito

Rails: Super Cool Simple Column Sorting

Rails: Super Cool Simple Column Sorting
Ben Kittrell - 04 19, 2007 @ 01:17PM
Comments: 21

Of all the things I hate doing, column sorting has to be top on my list.  It always seems to be more difficult than it should be.  Here's my take on a really simple way to implement column sorting for your Rails application.  I call it Super Cool Simple Column Sorting, or SCSCS.  That's pronounced 'ssssssss'.

The first part is the HTML helper.

  def sort_link(title, column, options = {})
    condition = options[:unless] if options.has_key?(:unless)
    sort_dir = params[:d] == 'up' ? 'down' : 'up'
    link_to_unless condition, title, request.parameters.merge( {:c => column, :d => sort_dir} )
  end

    
It takes the title you wish to display, the name of the column you wish to sort, and any extra options you wish to add to the link.  You can also pass an :unless flag to turn the link on or off.  Put this in a helper somewhere, maybe application_helper.rb.

To use it...

  <%= sort_link 'Company', :company_name %>

 
This will default to sorting up, or ASC, and will toggle up or down automatically.

Now you just need the SQL helper.

  def sort_order(default)
      "#{(params[:c] || default.to_s).gsub(/[\s;'\"]/,'')} #{params[:d] == 'down' ? 'DESC' : 'ASC'}"
  end

    
Put this somewhere the controller can see it, maybe application.rb.  Then just call it in the query in your controller.

  def list
    @applications = Application.find(:all, :order => sort_order('created_at'))
  end

 
The sort_order method takes the default column you wish to sort on.  

I really like this simple helper based approach.  It's not quite as slick as making a plugin, but it's easier to tweak it for different cases, and less than 10 lines.

Tags: Rails, helpers
Comments: 21

Comments

1. Herb (link) - 05 28, 2007 @ 02:22PM

Seeing as its the first google hit and no one has posted a comment... thanks works great for me!

2. Simon - 06 13, 2007 @ 05:27AM

Isn't this basically asking for injection attacks?

3. Ben Kittrell - 06 13, 2007 @ 08:41AM

You're right, I should have clarified that I'm using this in a secured admin environment where that's not an issue. I've added a fix to the code.

4. Evan - 06 13, 2007 @ 12:06PM

Worked great for me too. Thanks!

5. Jon - 07 05, 2007 @ 01:31PM

This worked well for me also. Is the fix for the injection already attacks included above?

6. Ben Kittrell - 07 05, 2007 @ 02:15PM

@Jon

Yes, the gsub call will protect against injection.

7. Rich Allen (link) - 12 16, 2007 @ 05:59AM

Exactly what I have been looking for, thanks!

8. Derrick - 12 21, 2007 @ 09:37AM

Great work, I was getting very fustrated searching feverishly for a simple solution that I could understand upon first clance, instead of the more complex and convoluted plugins everyone else is tring to viral around.

9. Thai - 03 20, 2008 @ 05:51PM

"Yes, the gsub call will protect against injection."

Can you explain how this would prevent injection?

Great code, btw. I was able to integrate this with faster pagination too!

10. Ljuba - 07 23, 2008 @ 07:52PM

Question: How do I sort by something's name when all I have is their ID number.

Say, if in addition to being able to sort by company name I'd like to sort by a value not contained in that model, but was identified by ID in a linking table.

How would the following line of code be different?

<%= sort_link 'Company', :company_name %>

The line of code above expects there to be parameter called company_name in whatever model you're talking about. All I have is an ID that links to another model.

Any help?

11. Mike M - 08 19, 2008 @ 09:59AM

I tried implementing this and it does not work. The link is active but it does not sort by Company. My field name is company in my database so I changed it in the sort_link. Anything else that I did wrong?

12. Aamer Abbas - 08 28, 2008 @ 11:09AM

This code is not bad, but a few suggestions for the SQL injection issue:

1.) Don't use gsub to fix the string. Instead use ActiveRecord::Base.connection.quote - this will automatically use your database's escaping conventions.

2.) Better yet, to be absolutely certain, you could just limit sorting to a set number of fields that you define in an array. If it doesn't match one of those fields, then just use the default column.

13. rheaghen - 09 10, 2008 @ 11:33AM

I really love my dojo toolkit widgets. when it comes to column sorting, I thinks its best to let the users machine do the work.

hers a great example app of column sorting with dojo.

(link)

-Ryan

14. Ben - 09 10, 2008 @ 01:36PM

That's cool, it wouldn't work in this instance however, because pagination is required. Javascript pagination wouldn't work either, cause loading 8000 records and parsing xml for each of them is not good for the environment.

I wish I had found dojocampus a week ago. I tried out dojo and got so frustrated trying to find documentation and examples I just went back to Prototype.

15. JVandaL - 12 09, 2008 @ 04:01PM

What about ordering through associations? What changes could be added to do this.

I think this is the same question that Ljuba asked several months ago

16. JVandaL - 12 09, 2008 @ 04:08PM

Also what about sorting on multiple fields. If I do the following
find(:all, :order => sort_order('first, second, third')

the sort_order helper generates this in the SQL
ORDER BY first,second,third ASC

but it seems to only be ordering by first not any of the others...
Any help?

17. JVandaL - 12 09, 2008 @ 06:57PM

I figured out a solution. It's kind of ugly but it seems to work ok.

def sort_order(default)
# allows for more than one column to sort on
sql_order = [ ]
sort_array = (params[:c] || default.to_s).gsub(/[\s;'\"]/,'').split(/,/)
sort_array.each do |c|
sql_order << c + " #{params[:d] == 'down' ? 'DESC' : 'ASC'}"
end
sql_order.join(', ')
end

and for my first question about associations I did this

[...] .find(:all, :include => [:customer], :order => sort_order('created_at'))

and then with both of these changes I could do this

<%= sort_link 'Customer', 'customers.first_name, customers.last_name' %>

I know this is an old blog entry, but if anyone is out there I'd love any feed back.

18. wjm - 12 29, 2008 @ 11:21PM

Works great and a perfect solution for my VPN only application - thanks.

19. wjm - 12 30, 2008 @ 12:07AM

Thought this would be useful for others. I've added arrows to indicate the direction of the sort by doing the following:

NEXT TO SORT_LINK -> &nbsp;<%= arrow %>

IN THE HELPER ->
def arrow
params[:d]== "down" ? "&#8595;" : "&#8593;"
end

20. Lukas Oberhuber (link) - 03 17, 2009 @ 08:46AM

I think the sort_link function needs a small change to get default sorting order working:
def sort_link(title, column, options = {})
condition = options[:unless] if options.has_key?(:unless)
sort_dir = options[:d] if options.has_key?(:d)
sort_dir = params[:d] == 'up' ? 'down' : 'up' if params[:d]
link_to_unless condition, title, request.parameters.merge( {:c => column, :d => sort_dir} )
end

21. Alfie - 06 04, 2009 @ 08:50AM

This is exactly what I need. However, I'm having a few issues. My 'find' is in the model:
-------------
def find_products
scope = Model.scoped({:order => sort_order('attribute_1')})
scope = scope.scoped :conditions => ...
end

def sort_order(default)
"#{(params[:c] || default.to_s)} #{params[:d] == 'down' ? 'DESC' : 'ASC'}"
end
----------
My problem is that I can't figure out how to pass params to the model. It doesn't recognize it as a method or variable and I don't completely understand the last line in the sort_link method (ink_to_unless condition, title, request.parameters.merge( {:c => column, :d => sort_dir} )). What is the best way to get this to work? Any advice would be appreciated. Otherwise, thanks for the headstart in tackling this.

Post a Comment




powered by Doodlekit™ Free Website Builder by Doodlebit™ Website Company