Thinking Sphinx - multiple conditions for attribute filters using OR

Question

I have my method to search, which looks like this:

class Machine < ActiveRecord::Base
  def filter_search(params, order_query = 'machines.updated_at desc')
      ts_query = ''
      attribute_filters = { order: order_query.gsub(/.*?\./, ''),
                            conditions: {},
                            with: {},
                            limit: 50000,
                            max_matches: Machine::MAX_TS_MATCHES
                          }

      # ... more methods dismissed      

      if params[:currency].present?
        with_null_currency = "*, IF(currency_attr = #{params[:currency]} OR currency_attr = 0, 1, 0) as my_currency"
        attribute_filters[:select] = with_null_currency
        attribute_filters[:with][:my_currency] = 1
      end

      if params[:machine_ad_type_rent] == 'rent' && params[:machine_ad_type_sale].blank?
        with_rent_ad_mask = "*, IF(ad_mask_attr = 1 OR ad_mask_attr = 3, 1, 0) as my_rent_ad_mask"
        attribute_filters[:select] = with_rent_ad_mask
        attribute_filters[:with][:my_rent_ad_mask] = 1
      end

      ids = Machine.search_for_ids(Riddle::Query.escape(ts_query), attribute_filters)
      machines = machines.where(id: ids).order(order_query)
  end
end

These conditions work well separately:

2.0.0p353 :005 > Machine.filter_search(currency: 1)
  Sphinx Query (6.8ms)  SELECT *, IF(currency_attr = 1 OR currency_attr = 0, 1, 0) as my_currency FROM `machine_core` WHERE `my_currency` = 1 AND `sphinx_deleted` = 0 ORDER BY `updated_at` desc LIMIT 0, 50000 OPTION max_matches=500000
  Sphinx  Found 85 results

2.0.0p353 :006 > Machine.filter_search(machine_ad_type_rent: 'rent')
  Sphinx Query (7.0ms)  SELECT *, IF(ad_mask_attr = 1 OR ad_mask_attr = 3, 1, 0) as my_rent_ad_mask FROM `machine_core` WHERE `my_rent_ad_mask` = 1 AND `sphinx_deleted` = 0 ORDER BY `updated_at` desc LIMIT 0, 50000 OPTION max_matches=500000
  Sphinx  Found 118 results

But if I try to search by both of these params, it fails:

2.0.0p353 :007 > Machine.filter_search(machine_ad_type_rent: 'rent', currency: 1)
  Sphinx  Retrying query "SELECT *, IF(ad_mask_attr = 1 OR ad_mask_attr = 3, 1, 0) as my_rent_ad_mask FROM `machine_core` WHERE `my_currency` = 1 AND `my_rent_ad_mask` = 1 AND `sphinx_deleted` = 0 ORDER BY `updated_at` desc LIMIT 0, 50000 OPTION max_matches=500000; SHOW META" after error: index machine_core: no such filter attribute 'my_currency' - SELECT *, IF(ad_mask_attr = 1 OR ad_mask_attr = 3, 1, 0) as my_rent_ad_mask FROM `machine_core` WHERE `my_currency` = 1 AND `my_rent_ad_mask` = 1 AND `sphinx_deleted` = 0 ORDER BY `updated_at` desc LIMIT 0, 50000 OPTION max_matches=500000; SHOW META
  Sphinx  Retrying query "SELECT *, IF(ad_mask_attr = 1 OR ad_mask_attr = 3, 1, 0) as my_rent_ad_mask FROM `machine_core` WHERE `my_currency` = 1 AND `my_rent_ad_mask` = 1 AND `sphinx_deleted` = 0 ORDER BY `updated_at` desc LIMIT 0, 50000 OPTION max_matches=500000; SHOW META" after error: index machine_core: no such filter attribute 'my_currency' - SELECT *, IF(ad_mask_attr = 1 OR ad_mask_attr = 3, 1, 0) as my_rent_ad_mask FROM `machine_core` WHERE `my_currency` = 1 AND `my_rent_ad_mask` = 1 AND `sphinx_deleted` = 0 ORDER BY `updated_at` desc LIMIT 0, 50000 OPTION max_matches=500000; SHOW META
  Sphinx Query (16.5ms)  SELECT *, IF(ad_mask_attr = 1 OR ad_mask_attr = 3, 1, 0) as my_rent_ad_mask FROM `machine_core` WHERE `my_currency` = 1 AND `my_rent_ad_mask` = 1 AND `sphinx_deleted` = 0 ORDER BY `updated_at` desc LIMIT 0, 50000 OPTION max_matches=500000
ThinkingSphinx::SphinxError: index machine_core: no such filter attribute 'my_currency' - SELECT *, IF(ad_mask_attr = 1 OR ad_mask_attr = 3, 1, 0) as my_rent_ad_mask FROM `machine_core` WHERE `my_currency` = 1 AND `my_rent_ad_mask` = 1 AND `sphinx_deleted` = 0 ORDER BY `updated_at` desc LIMIT 0, 50000 OPTION max_matches=500000; SHOW META
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/thinking-sphinx-3.1.1/lib/thinking_sphinx/connection.rb:91:in `rescue in query'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/thinking-sphinx-3.1.1/lib/thinking_sphinx/connection.rb:94:in `query'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/thinking-sphinx-3.1.1/lib/thinking_sphinx/connection.rb:75:in `query_all'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/thinking-sphinx-3.1.1/lib/thinking_sphinx/search/batch_inquirer.rb:17:in `block in results'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/thinking-sphinx-3.1.1/lib/thinking_sphinx/connection.rb:37:in `block in take'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/innertube-1.1.0/lib/innertube.rb:138:in `take'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/thinking-sphinx-3.1.1/lib/thinking_sphinx/connection.rb:35:in `take'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/thinking-sphinx-3.1.1/lib/thinking_sphinx/search/batch_inquirer.rb:16:in `results'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/thinking-sphinx-3.1.1/lib/thinking_sphinx/middlewares/inquirer.rb:9:in `block in call'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/activesupport-4.1.1/lib/active_support/notifications.rb:159:in `block in instrument'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/activesupport-4.1.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/activesupport-4.1.1/lib/active_support/notifications.rb:159:in `instrument'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/thinking-sphinx-3.1.1/lib/thinking_sphinx/logger.rb:3:in `log'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/thinking-sphinx-3.1.1/lib/thinking_sphinx/middlewares/inquirer.rb:8:in `call'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/thinking-sphinx-3.1.1/lib/thinking_sphinx/middlewares/geographer.rb:11:in `call'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/thinking-sphinx-3.1.1/lib/thinking_sphinx/middlewares/sphinxql.rb:14:in `call'
... 16 levels...
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/railties-4.1.1/lib/rails/commands/console.rb:9:in `start'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/railties-4.1.1/lib/rails/commands/commands_tasks.rb:69:in `console'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/railties-4.1.1/lib/rails/commands/commands_tasks.rb:40:in `run_command!'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/railties-4.1.1/lib/rails/commands.rb:17:in `<top (required)>'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/activesupport-4.1.1/lib/active_support/dependencies.rb:247:in `require'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/activesupport-4.1.1/lib/active_support/dependencies.rb:247:in `block in require'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/activesupport-4.1.1/lib/active_support/dependencies.rb:232:in `load_dependency'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/activesupport-4.1.1/lib/active_support/dependencies.rb:247:in `require'
    from /Users/serj/Projects/gearup/bin/rails:8:in `<top (required)>'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/activesupport-4.1.1/lib/active_support/dependencies.rb:241:in `load'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/activesupport-4.1.1/lib/active_support/dependencies.rb:241:in `block in load'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/activesupport-4.1.1/lib/active_support/dependencies.rb:232:in `load_dependency'
    from /Users/serj/.rvm/gems/ruby-2.0.0-p353@gearup/gems/activesupport-4.1.1/lib/active_support/dependencies.rb:241:in `load'
    from /Users/serj/.rvm/rubies/ruby-2.0.0-p353/lib/ruby/site_ruby/2.0.0/rubygems/core_ext/kernel_require.rb:55:in `require'
    from /Users/serj/.rvm/rubies/ruby-2.0.0-p353/lib/ruby/site_ruby/2.0.0/rubygems/core_ext/kernel_require.rb:55:in `require'

Is there any way to use it together and still perform single Thinking Sphinx query to search?

Answer 1

The trick here is that you'll need to compose your SELECT clause in pieces:

select = ['*']

if params[:currency].present?
  select << "IF(currency_attr = #{params[:currency]} OR currency_attr = 0, 1, 0) as my_currency"
  attribute_filters[:with][:my_currency] = 1
end

if params[:machine_ad_type_rent] == 'rent' && params[:machine_ad_type_sale].blank?
  select << "IF(ad_mask_attr = 1 OR ad_mask_attr = 3, 1, 0) as my_rent_ad_mask"
  attribute_filters[:with][:my_rent_ad_mask] = 1
end

attribute_filters[:select] = select.join(', ')

ids = Machine.search_for_ids(Riddle::Query.escape(ts_query), attribute_filters)