Sphinx searching on migrated application data failing

Question

A rails 3.2.18 application is created on migrated data rails 2.3.10 app. Data is fully indexed now (thanks Pat!) however searches are tripping up (either due to legacy SphinxQL protocol or legacy application code).

I've managed to run an initializer following the info from this release note (sphinx is 2.1.8 and ts is 3.1.1) been attempting to look in the mirror and tell that fellow to RTFM

ThinkingSphinx::SphinxQL.functions!

and added to the thinking_sphinx.yml the utf bit

  bin_path: /usr/local/bin
  pid_file: /Users/me/r/fna/shared/tmp/searchd.pid
  configuration_file: /Users/me/r/fna/config/development.sphinx.conf
  log: /Users/me/r/fna/log/searchd.log
  query_log: /Users/me/r/fna/log/searchd.query.log
  indices_location: /Users/me/r/fna/shared/sphinx
  64bit_timestamps: true
  utf8: true
  enable_star: true
  min_infix_len: 2
  max_matches: 1000
  mysql41: 9312
  mem_limit: 128M

The following error occurs when a search is run within the application Sphinx Query (59.7ms) SELECT * FROM filodiretto_core WHERE MATCH('@filodirettotype_id 1 @site_id 1') AND sphinx_deleted = 0 ORDER BY @rank DESC LIMIT 0, 10 OPTION max_matches=1000 Completed 500 Internal Server Error in 126.4ms

ThinkingSphinx::SyntaxError (sphinxql: syntax error, unexpected USERVAR, expecting IDENT (or 6 other tokens) near '@rank DESC LIMIT 0, 10 OPTION max_matches=1000; SHOW META' - SELECT * FROM `filodiretto_core` WHERE MATCH('@filodirettotype_id 1 @site_id 1') AND `sphinx_deleted` = 0 ORDER BY @rank DESC LIMIT 0, 10 OPTION max_matches=1000; SHOW META):
  app/controllers/filodirettos_controller.rb:22:in `risultati'

That relevant controller statement is

    @results = @filodiretto_search.search.order('data_timestamp DESC')

which is governed by

class FilodirettoSearch < BaseSearch
  accept :filodirettotype, :filodirettotype_id, :titolo, :domanda, :risposta, :site_id, :data, :terms
end

which in turn inherits form the class

class BaseSearch
  PER_PAGE = 10

  class_attribute :accepts, :base_class, :base_param, :per_page

  attr_accessor :params

  def self.accept(*accepts)
    self.accepts = accepts
    accepts += [:page]
    self.send(:attr_accessor, *accepts)
  end

  def self.set_base_class(klass)
    self.base_class = klass
  end

  def self.set_base_param(name)
    self.base_param = name.to_sym
  end

  def self.set_per_page(per_page)
    self.per_page = per_page
  end

  def base_param
    self.class.base_param ||= self.class.to_s.underscore.to_sym
  end

  def base_class
    @base_class ||= Kernel.const_get(self.class.to_s.sub('Search', ''))
  end

  def per_page
    self.class.per_page ||= PER_PAGE
  end

  def initialize(params)
    self.params = params

    self.class.accepts.each do |param|
      param_value = params[base_param][param]
      if param.to_s =~ /id/
        param_value = param_value.to_i unless param_value.blank?
      end

      instance_variable_set("@#{param}", param_value)
    end
    self.page = params[:page]
  end

  def search(options = {})
    extra_conditions = options.delete(:conditions) || {}
    order = options.delete(:order) || '@rank DESC'
    extra_with = options.delete(:with) || {}

    with = search_with.merge(extra_with)
    conditions = search_conditions.merge(extra_conditions)

    base_class.search sanitized_terms, :conditions => conditions, :with => with, :page => page, :per_page => per_page, :sort_mode => :extended, :order => order, :retry_stale => true, :match_mode => :extended
  end

  def sanitized_terms
    @terms ||= ""
    sanitize(@terms)
  end

  def sanitize(s)
    if s.respond_to? :gsub
      s.gsub("/", "\\/")
    else
      s
    end
  end

  def search_with
    with = {}
    if params[base_param][:with]
      params[base_param][:with].each do |k, v|
        with[k] = case v
        when 'true'
          true
        when 'false'
          false
        else
          v
        end
      end
    end
    with
  end

  def search_conditions
    conditions = {}
    self.class.accepts.each do |condition|
      if condition.to_s != 'terms'
        conditions[condition] = sanitize(self.send(condition)) unless self.send(condition).blank?
      end
    end
    conditions
  end
end

Update
I see how the syntactic approach can change things quite a bit. Possibly the whole reasoning needs a re-think. Previously I had a model_search which would define which attributes to accept

  accept :filodirettotype, :filodirettotype_id, :titolo, :domanda, :risposta, :site_id, :data, :terms

which is a bit of a tautology with the model_index definition.
Now assuming I want to do a search on the an ID and general terms

collection_select :filodiretto_search, :filodirettotype_id, Filodirettotype.all, :id, :name
text_field :filodiretto_search, :terms, :size => 35

where the HABM for filodirettotype is indexed

has filodirettotypes(:id), :as => :filodirettotype_id

It appears that BaseSearch can be made redundant via a concise controller statement
Something like:

@filodiretto_search = FilodirettoSearch.new params
@results = Filodiretto.search(params[:filodiretto_search][:terms], :with => {:filodirettotype_ids => params[:filodiretto_search][:filodirettotype_id]}) 

with only Filodiretto.search(params[:filodiretto_search][:terms]) proper results are generated. But the conditional statement generates:

Sphinx Query (1.0ms)  SELECT * FROM `filodiretto_core` WHERE MATCH('solare @filodirettotype_id 1') AND `sphinx_deleted`

ActionView::Template::Error (index filodiretto_core: query error: no field 'filodirettotype_ids' found in schema

I don't see how the mechanism functions past the single string criteria

Update 2 Pluralization with HABTM. The index definition needed the as symbol to be plural as well.

    has filodirettotypes(:id), :as => :filodirettotype_ids

And controller must invoke an array:

@results = Filodiretto.search(params[:filodiretto_search][:terms], :with => {:filodirettotype_ids => [params[:filodiretto_search][:filodirettotype_id]]}) 

Not too keen on having an if statement in the controller for a value ==""
but functions...

Answer 1

Two things that should help fix this:

• You're referring to @rank in the search method of BaseSearch - with the newer SphinxQL syntax, that should be weight().
• You're calling order method (ActiveRecord-style) instead of passing your custom order statement through the search call via the :order option.