Topic: Sorting with associations and multiple columns

So I had a need to be able to sort a table and I didn't want to use all the fancy AJAX ways people have developed. I eventually came across this (rails super cool simple column sorting) and tried it out.

I ran into two problems I needed a way to sort through associations and I need to be able to sort by more than one column in certain cases.

For the first issue I found this (How do you order through associations?) on the forums. With some tweaking here and there I eventually came out with the following. I feel like it's a bit ugly and I know I'm still new to Ruby so I thought some experts could offer their tips.

# application.rb 
  def sort_order(default)
    # The original code was the following
    # "#{(params[:c] || default.to_s).gsub(/[\s;'\"]/,'')} #{params[:d] == 'down' ? 'DESC' : 'ASC'}"
    # The new code is to allow 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

# application_help.rb
  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

# A normal use case in a controller
@projects = Project.find(:all, :order => sort_order('created_at'))

# A weird case with associations and multiple columns
@requirements = requirements.find(:all, :include => [:product], :order => sort_order('denied, approved, requested'))

# A normal use case in a view
<%= sort_link 'Project', :name %>

# A weird case with associations and multiple columns
<%= sort_link 'Product : Version', 'products.name, products.version' %>


All of this works but is there a better way that keeps things simple and DRY?

TIA
Jack

Re: Sorting with associations and multiple columns

That all seems quite complicated - i normally just do something like this:

#sort user's resources by the rank of their labellings (and then by name if rank's the same)

Resource.find(:all, :conditions => ["resources.user_id = ?", user.id], :include => [:labellings], :order => "labellings.rank, resources.name")

Is that not DRY?  I dunno...

###########################################
#If i've helped you then please recommend me at Working With Rails:
#http://www.workingwithrails.com/person/ … i-williams

Re: Sorting with associations and multiple columns

Yes Max that is what I was doing before. However, the sort_link helper method generates a link for the column title that allows the user to change what they sort the table by. The sort_order helper generates an SQL fragment based on which link was clicked. Most people do column sorting with AJAX but I didn't wish to go this route. The above method simply recalls the page and merges a few things in the params hash (c = column, d = direction)

I hope that clears up what I am try to do. Like I said it works fine just wanted to see if I should be doing anything better.

for example perhaps improving the way I generate the sql fragment

...
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(', ')
...

Re: Sorting with associations and multiple columns

Just a bump to get above that spam post. On a related note. Shouldn't the moderators delete those and perhaps ban the users?