I want to find all records in a model that contains the email
“[email protected]” despite the fact that the email value is within an
array of hashes. How do I do this?
I have a table of email messages like so:
create_table :emails do |t|
t.string :emailMessageId
t.datetime :date
t.string :subject
t.string :gmailMessageId
t.string :gmailThreadId
t.string :from_hash, :default => nil
t.text :to_hash, :default => nil
t.text :cc_hash, :default => nil
t.integer :contact_id
The email.rb model file says:
class Email < ActiveRecord::Base
serialize :from_hash, Hash
serialize :to_hash, Array
serialize :cc_hash, Array
end
Imagine that
:to_hash = {“name” => “john”, “email” => “[email protected]”}
or an array of hashes
:to_hash = [ {“name” => “john”, “email” => “[email protected]”}, {“name”
=> “bob”, “email” => “[email protected]”} ]
As an example, here is Email.first
#<Email id: 1, emailMessageId: “357”, date: “2011-10-03 00:39:00”,
subject: nil, gmailMessageId: nil, gmailThreadId: nil, from_hash:
{“name”=>“melanie”, “email”=>“[email protected]”}, to_hash:
[{“name”=>“michie”, “email”=>“[email protected]”}, {“name”=>“clarisa”,
“email”=>“[email protected]”}], cc_hash: [{“name”=>“john”,
“email”=>“[email protected]”}, {“name”=>“alex”,
“email”=>“[email protected]”}], contact_id: 1, created_at: “2011-10-03
00:39:00”, updated_at: “2011-10-03 00:39:00”>
Further imagine that my database has thousands of such records, and I
want to pull all records keyed on :to_hash[“email”].
I tried variations on:
hash = {“name” => “john”, “email” => “[email protected]”}
Email.find(:all, :conditions => [“to_hash = ?”, hash]) # returns the
following error
ActiveRecord::StatementInvalid: SQLite3::SQLException: near “,”:
syntax error: SELECT “emails”.* FROM “emails” WHERE (to_hash = '—
- name
- john
‘,’— - [email protected]
')
I also tried:
emale = “[email protected]”
Email.find(:all, :conditions => [“to_hash = ?”, emale])
=> [], which is not an error, but not what I want either!
And finally:
emale = “[email protected]”
Email.find(:all, :conditions => [“to_hash[‘name’] = ?”, emale])
which, as expected, gave me a syntax error…
ActiveRecord::StatementInvalid: SQLite3::SQLException: near
“[‘name’]”: syntax error: SELECT
“emails”.* FROM “emails” WHERE (to_hash[‘name’] = ‘[email protected]’)