thinking sphinx ordering by mixing capitals and lower case

Question

I have a rails application that is using thinking_sphinx for the searching. My problem is that the result returned is sorted with capitals first and lower case after it at the bottom. I'd like to mix them so that both 'A' and 'a' comes before 'B'. This is the method I'm using:

Company.search(query, :star => true, :page => params[:page], :per_page => 20, :order => :name, :sort_mode => :asc)

Answer 1

Maurício's answer is almost on the right track - but that's for transforming the text that Sphinx indexes for fields. When you're sorting, you're doing that by attributes (which don't use the charset table transformations).

What you'll need to do is separate the sorting attribute of name out from the field, and force the attribute version to use lowercase:

indexes :name
has "LOWER(companies.name)", :as => :name_sort

And then searching becomes:

Company.search query,
  :star      => true,
  :page      => params[:page],
  :per_page  => 20,
  :order     => :name_sort,
  :sort_mode => :asc