ernie/meta_search — Issue #92: Problem with meta_search and MySQL
Author: jamgregory
Created: 2012-02-08 18:38:50+00:00
State: open

I'm not sure if anyone else has come across this problem, but meta_search (or something meta_search ties into) is generating invalid SQL queries in relation to MySQL databases.

I have a table called Teacher and a column called Firstname. From the console (or my Rails app), if I try to sort using the Firstname column, I get a MySQL error returned.

ruby-1.9.2-p180 :006 > Teacher.search({'meta_sort' => 'Surname.asc'})
Teacher Load (1.5ms) SELECT `teachers`.* FROM `teachers` WHERE `teachers`.`Deactivated` = 0 ORDER BY "teachers"."Surname" ASC
Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '."Surname" ASC' at line 1: SELECT `teachers`.* FROM `teachers` WHERE `teachers`.`Deactivated` = 0 ORDER BY "teachers"."Surname" ASC
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '."Surname" ASC' at line 1: SELECT `teachers`.* FROM `teachers` WHERE `teachers`.`Deactivated` = 0 ORDER BY "teachers"."Surname" ASC
from /usr/share/ruby-rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.1.3/lib/active_record/connection_adapters/mysql2_adapter.rb:687:in `query'
from /usr/share/ruby-rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.1.3/lib/active_record/connection_adapters/mysql2_adapter.rb:687:in `block in exec_query'
from /usr/share/ruby-rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.1.3/lib/active_record/connection_adapters/abstract_adapter.rb:244:in `block in log'
from /usr/share/ruby-rvm/gems/ruby-1.9.2-p180/gems/activesupport-3.1.3/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
from /usr/share/ruby-rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.1.3/lib/active_record/connection_adapters/abstract_adapter.rb:239:in `log'
from /usr/share/ruby-rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.1.3/lib/active_record/connection_adapters/mysql2_adapter.rb:685:in `exec_query'
from /usr/share/ruby-rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.1.3/lib/active_record/connection_adapters/mysql2_adapter.rb:679:in `select'
from /usr/share/ruby-rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.1.3/lib/active_record/connection_adapters/abstract/database_statements.rb:18:in `select_all'
from /usr/share/ruby-rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.1.3/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
from /usr/share/ruby-rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.1.3/lib/active_record/base.rb:470:in `find_by_sql'
from /usr/share/ruby-rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.1.3/lib/active_record/relation.rb:111:in `to_a'
from /usr/share/ruby-rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.1.3/lib/active_record/relation.rb:129:in `to_a'
from /usr/share/ruby-rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.1.3/lib/active_record/relation.rb:436:in `inspect'
from /usr/share/ruby-rvm/gems/ruby-1.9.2-p180/gems/railties-3.1.3/lib/rails/commands/console.rb:45:in `start'
from /usr/share/ruby-rvm/gems/ruby-1.9.2-p180/gems/railties-3.1.3/lib/rails/commands/console.rb:8:in `start'
from /usr/share/ruby-rvm/gems/ruby-1.9.2-p180/gems/railties-3.1.3/lib/rails/commands.rb:40:in `'
from script/rails:6:in `require'
from script/rails:6:in `

'

Basically, the meta_search component is generating string literals "Teacher" and "Firstname", which aren't valid. It should be enclosing Teacher and Firstname in backticks.