can you translate this Rails code for MySQL:
def Customer.find_by_key(key)
Customer.find(:all, :conditions => "pre_name REGEXP '.*#{key}.*'
OR sur_name REGEXP '.*#{key}.*'
OR street REGEXP '.*#{key}.*'
OR zip REGEXP '.*#{key}.*'
OR city REGEXP '.*#{key}.*'
OR phone_1 REGEXP '.*#{key}.*'
OR phone_2 REGEXP '.*#{key}.*'
OR email REGEXP '.*#{key}.*'
OR company REGEXP '.*#{key}.*'")
end
to SQlite3 code?
The REGEXP function isn't defined by default in sqlite3, you'll have to do a bit of work before.
Stick this in an initializer (for example config/initializers/sqlite_regexp.rb), works with rails 3.1 (see below for rails 3.0):
require 'active_record/connection_adapters/sqlite3_adapter'
class ActiveRecord::ConnectionAdapters::SQLite3Adapter
def initialize(db, logger, config)
super
db.create_function('regexp', 2) do |func, pattern, expression|
regexp = Regexp.new(pattern.to_s, Regexp::IGNORECASE)
if expression.to_s.match(regexp)
func.result = 1
else
func.result = 0
end
end
end
end
Code stolen here.
You could of course rewrite your query as in @mu's answer, but I figured it would be nice to know how to really implement the function.
Update
The code above doesn't work for rails 3.0, this should work:
require 'active_record/base'
require 'active_record/connection_adapters/sqlite_adapter'
module ActiveRecord::ConnectionAdapters
class SQLite3Adapter < SQLiteAdapter
def initialize(db, logger, config)
super
db.create_function('regexp', 2) do |func, pattern, expression|
regexp = Regexp.new(pattern.to_s, Regexp::IGNORECASE)
if expression.to_s.match(regexp)
func.result = 1
else
func.result = 0
end
end
end
end
end
/var/lib/gems/1.8/gems/activerecord-3.0.7/lib/active_record/base.rb:1009:in `method_missing': undefined local variable or method `establish_connection' for ActiveRecord::Base:Class (NameError)
Replace these:
pre_name REGEXP '.*#{key}.*'
with LIKE:
pre_name LIKE '%#{key}%'
Or better, use placeholders:
:conditions => [
"pre_name LIKE :pattern OR sur_name LIKE :pattern ...",
{ :pattern => '%' + key + '%' }
]
SQLite understands REGEXP but it is not implemented by default, you have to implement it yourself. You could add your implementation but there's no point when LIKE will probably do the job.