With Rails 3, Ruby 1.9.2, how do I make a table sortable on a column that is NOT a DB column?

It is ea开发者_开发问答sy, following this railscast http://railscasts.com/episodes/240-search-sort-paginate-with-ajax to be able to create a table that sorts based on columns that exist in my database. but I have other columns I show in my table that are not database columns and I still need to sort by those columns.

For example, I have multiple columns to make up a donors name (prefix1, first_name1,middle_name1,last_name1, suffix1, prefix2, first_name2,middle_name2,last_name2,suffix2, company). I defined in my model "who_donated" which is a combination of the actual table columns using some rules I was given. So basically my donors table I display has 1 field for who_donated and I would like to be able to sort on that field.

How can I sort on a column of my view table if that column isn't an actual column in my database table?

Because I had so many different ways of gathering information to put in the columns I need to sort by and because there will be many 100ks of rows possibly to sort by, I decided to put as much of the work on the mysql server as possible to do the sorting for every case.

This is how I solved it... I pretty much followed railscasts 228 and 240 for the initial setup:http://asciicasts.com/episodes/228-sortable-table-columns and http://asciicasts.com/episodes/240-search-sort-paginate-with-ajax

Then I made a few modifications as follows.

donors_controller:

def last_sort_column
  params[:sort].blank? ? 'created_at' :  params[:sort]
end 

def sort_column
  case 
    when Donor.column_names.include?(params[:sort]) then params[:sort]
    when params[:sort] == 'Donors' then 
        'concat(prefix1,first_name1,middle_name1,last_name1,suffix1,
        prefix2,first_name2,middle_name2,last_name2,suffix2,company)' 
    when params[:sort] == 'First Donated' then
         '(select min(donations.created_at) from donations where donations.donor_id = donors.id)'       
    when params[:sort] == 'Last Donated' then
         '(select max(donations.created_at) from donations where donations.donor_id = donors.id)'       
    when params[:sort] == 'Times Donated' then
         '(select count(*) from donations where donations.donor_id = donors.id)'       
    when params[:sort] == 'Total Amount' then
         '(select sum(amount) from donations where donations.donor_id = donors.id)'       
    else 'created_at'
  end
end

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

and application_helper:

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

The addition of the last_sort_column is because sort_column that gets sent into the order by statement doesn't match the column name so it never remembered what you have sorted as asc or desc.

This allows me to put as crazy of a sql statement as I want into the sort, but still completely prevents any possibility of sql injection since the url will only show the actual names of the columns. If something other than the valid options I have allowed are entered, it defaults to created_at and throws away the bogus values.

I was actually surprised at how fast this was sorting over 100k rows linked to another 200k rows.

Simplest case:

<% sorted_donors = @unsorted_donors.sort_by{ |donor| donor.who_donated } %>

Read more about sort_by here: http://www.ruby-doc.org/core-1.9.2/Enumerable.html#method-i-sort_by

Like I said, throwing this code directly into a view is the ugliest, yet easiest way of getting the sort you want. If you're doing pagination, you'll need to do the sorting in the controller before paginating, etc... but this snippet should at least get you moving in the right direction.