thinking sphinx ordering by mixing capitals and lo

问题:

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)

回答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

回答2:

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.

回答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