Ordering by count with Thinking Sphinx

Question

I want my search engine to be able to order Lawyers on the count of cases of a certain case type. The most a lawyer has finalized cases of a certain type, the higher he will be ranked.

lawyer.rb

has_many :cases    
has_many :case_types, :through => :cases

define_index do
        indexes case_types.name, :as => :case_types
        has case_types(:id), :as => :case_types_id
        has "SUM(case_types)", :as => :case_type_count #this line gives an error,  as my lawyer table does't have a case_type column, also, I need to count DISTINCT case_types
      end

In my search_controller.rb, I would like to do something like that, suggestion being the name of a case type

@lawyers = Lawyer.search params[:suggestion], :order => "@case_type_count DESC"

Am I going the wrong way? should I think of a less Sphinx oriented method? The problem is I need to do an each_with_geodist on @lawyers, so I would need to get my lawyers through a Sphinx search.

Answer 1

Add the following to your define_index:

has "COUNT(case_types.id)", :as => :case_type_count, :type => :integer

join case_types

Then retrieve by case_count:

Lawyer.search("", :order => "case_type_count desc")

I have found it useful to read the sql code in development.sphinx.conf which allows me to see the column names being generated.