thinking sphinx ordering by mixing capitals and lo

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

You have to configure sphinx to transform the input, making uppercase letters become lowercase letters. You can do this with the "charset_table" config.

And here's a complementary tutorial on how to configure it with thinking_sphinx.

Answer 2

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

Answer 3

After researching many hours I got simple and elegant solution for case insensitive search. Just define your index in following way.

indexes name, as: :name_sort, sortable: :insensitive