Thinking Sphinx sorting problem

Rails
1

Hello all,

I’m trying to get some records in my application to sort based on a SQL
calculated value and am getting no records returned. I have checked and
double checked and the query that TS is indexing is working out, but the
problem is with the controller.

Here is what I’m indexing:

indexes “((#{AppSystem::Income::BASE} - applications.monthly_income *
12)) +
SUM(#{AppSystem::Income::ADDITIONAL} -
(COALESCE(household_members.monthly_income, 0) * 12))”,
:sortable => true, :as => :applicant_need, :joins =>
:household_members,
:type => :float

It’s a pretty complex index, but again it is working. It creates an
index of a float that is a Max income to qualify (MIQ) minus Total
Household Income (THI) MIQ - THI and this is what is indexed.

In my controller, I have a search action:

def search
@searched = params[:search]
@sort = params[:sort_by].to_i
@filter = params[:filter_by].to_i

application_ids = Application.search_for_ids(@searched,
  :select => "id, applicant_id",
  :group_by => 'applicant_id', :group_function => :attr,
  :with => Application.filter_for_search(@filter, @season),
  :sort_mode => :extended,
  :order => Application::Search::SortBy::SPHINX[@sort], # this gives

me sql string to sort by
:per_page => 1000)

@applicant_ids = Applicant.search_for_ids("@application_ids

‘#{application_ids * ‘|’}’",
:match_mode => :extended2, :page => params[:page], :per_page => 5,
:sort_mode => :extended,
:order => Application::Search::SortBy::SPHINX[@sort]) # this gives
me sql string to sort by

@applicants = @applicant_ids.empty? ? [] :

Applicant.find(@applicant_ids,
:select => “applicants.*, MAX(applications.created_at) AS
most_recent”,
:joins => [:applications],
:group => “applicants.id”,
:order => Application::Search::SortBy::VALUES[@sort]).to_a # this
gives me sql string to sort by

render :action => 'index'

end

When user sorts by last name related to application (first_name or
last_name, ASC or DESC) the sorting works fine. But sorting on
applicant_need (calculated float) I get no results.

Doing a “Rails.logger.info” check below each (application_ids,
@applicant_ids and @applicants) I get this:

++++ application_ids ++++
[4, 2, 1]
++++ @applicant_ids ++++
[] # all other sorting would have an array of ids here
++++ @applicant ++++
[]

I would appreciate any insight you might have. Thank you