In a RoR application I am making I have a sortable table made using a technique similar to this: http://dev.nozav.org/rails_ajax_table.html This is a two part question:
Normally the table is 'unsorted' when you get to it even though the items are alreay in alphabetical order according to the first column. Because of this, the first time you click title to sort by name it appears to do nothing because it was going from 'unsorted' to 'sorted by name' and both are identical. On the first click it should go from 'sorted by name' to 'reverse sorted by name' and the sort_td_class_helper should have the 'sortdown' class applied to it when the page loads. **A solution would either include sample code or enough explanation that I can get it to work.**
The other part of the question is how can I make the sorting case-insensive? **A solution would either include sample code or enough explanation that I can get it to work.**
People succeed in answering ParagramStudios's questions 26% of the time (9 successes in 34 attempts).
Answers by: Zachary Holt
Hi there,
First, you'll need to set the sort order on a default list:
class ItemController < ApplicationController
def list
params[:sort] = 'name' if params[:sort].nil?
...
end
end
I would recommend moving away from @params and starting to use params. Also, you'll probably want to start using symbols (:sort) instead of strings ("sort").
See http://weblog.rubyonrails.com/2006/04/25/use-params-not-params/
Second, this depends on the database. SQLite is case insensitive--in certain cases. (If you move to MySQL, you will get this by default.) If you stick with SQLite, then I believe you'll have to specify a collation. See http://www.sqlite.org/lang_select.html and http://www.sqlite.org/datatype3.html
It would be something like this:
@items_pages, @items = paginate :items, :order => "#{sort} COLLATE NOCASE", :conditions => conditions, :per_page => items_per_page
As a tip, I like to separate out my sort column from my sort order. If I can use a sort order that evaluates to true/false, then all I need to do to reverse it is !order. Anyway, food for thought.
Thanks for the quick response, I'll try your suggestions in the next few hours.
We're using a postgres database. Not sure if that changes anything.
Not terribly familiar with Postgres, but you can always force the lower case in the order option:
@items_pages, @items = paginate :items, :order => "lower(#{sort})", :conditions => conditions, :per_page => items_per_page
Note that this has the potential to break database agnosticism (I have no idea how many databases support lower()--MySQL has something very similar, if not identical).
This code lead to the code we used but we had to do some work to get it right. Cool if I reduce the reward to $25 before declaring you the winner? It did take another rails guy about an hour to get to a solution that worked.
$25 is fine if you post the code that worked.
Here you go...
def find_videos
videos_per_page = 6000
# note that title is lower-Casified
sort = case @params[:sort]
when "title" then "lower(title)"
when "dated_added" then "date_added"
when "run_time" then "duration"
when "size" then "size"
when "title_reverse" then "lower(title) DESC"
when "dated_added_reverse" then "dated_added DESC"
when "run_time_reverse" then "duration DESC"
when "size_reverse" then "size DESC"
else "lower(title)"
end
conditions = ["lower(title) LIKE lower(?) AND uploader_id = ?", "%#{@params[:query]}%", "#{User.current.id}"] #unless @params[:query].nil?
if @params[:query].nil?
conditions = ["uploader_id = ?", "#{User.current.id}"]
end
@total = Video.count(:conditions=>conditions)
@video_pages = Paginator.new self, @total, videos_per_page, @params['page']
@videos = Video.find :all,
#:limit => @video_pages.items_per_page,
#:offset => @video_pages.current.offset,
:conditions => conditions,
:order => sort
end