Ruby on Rails

I want to find all records in a model that contains the email
"john@test.com" 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" => "john@test.com"}
or an array of hashes

:to_hash = [ {"name" => "john", "email" => "john@test.com"}, {"name"
=> "bob", "email" => "bob@example.com"} ]
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"=>"mel@test.com"}, to_hash:
[{"name"=>"michie", "email"=>"mich@blah.com"}, {"name"=>"clarisa",
"email"=>"clarisa@123.com"}], cc_hash: [{"name"=>"john",
"email"=>"john@test.com"}, {"name"=>"alex",
"email"=>"alex@massimo.com"}], 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" => "john@test.com"}
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
- john@test.com
')
I also tried:

emale = "john@test.com"
Email.find(:all, :conditions => ["to_hash = ?", emale])
# => [], which is not an error, but not what I want either!
And finally:

emale = "john@test.com"
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'] = 'john@test.com')

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.