I have such index:
ThinkingSphinx::Index.define :service, with: :active_record do
# ...
has currency, as: :currency_attr
end
I need to be able to search by currency and also allow null values. So I try this solution (via official docs):
with_null_currency = "*, IF(currency = 1 OR currency IS NULL) AS my_currency"
Service.search('', :select => with_null_currency, :with => {'my_currency' => 1})
But it doesn't work somehow:
Sphinx Query (7.1ms) SELECT *, IF(currency = 1 OR currency IS NULL) AS my_currency FROM `service_core` WHERE `my_currency` = 1 AND `sphinx_deleted` = 0 LIMIT 0, 20 OPTION max_matches=500000
ThinkingSphinx::SyntaxError: sphinxql: syntax error, unexpected IDENT, expecting ')' or ',' near 'IS NULL) AS my_currency FROM `service_core` WHERE `my_currency` = 1 AND `sphinx_deleted` = 0 LIMIT 0, 20 OPTION max_matches=500000; SHOW META' - SELECT *, IF(currency = 1 OR currency IS NULL) AS my_currency FROM `service_core` WHERE `my_currency` = 1 AND `sphinx_deleted` = 0 LIMIT 0, 20 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'
... 6 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'
How can I make it work?
P.S. I use gem thinking-sphinx 3.1.1, Sphinx 2.1.4-release (rel21-r4421), PostgreSQL and Rails 4.1
I want to thank Pat for his help. The problem was that I am using alias in my index definition. That is why such error appeared. So, to solve this problem I need to use this line of code:
with_null_currency = "*, IF(currency_attr = 1 OR currency_attr = 0, 1, 0) as my_currency"
Service.search('', select: with_null_currency, with: {my_currency: 1})
Pat updated the docs to mention this issue.