Model.find(:all, :conditions ...) on one field for one key-value from a hash?

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"]. In other words, I want to be able to find all records in the Email model that contain the email "john@test.com" despite the fact that the email value is within an array of hashes. How do I do this?

I tried variations on:

 hash = {"name" => "john", "email" => "john@test.com"}
 Email.find(:all, :conditions => ["to_hash = ?", hash])  # returns the following error

 ActiveRecord::Statement开发者_C百科Invalid: SQLite3::SQLException: near ",": syntax error: SELECT   "emails".* FROM "emails" WHERE (to_hash = '--- 
 - name
 - john
 ','--- 
 - email
 - john@test.com
 ')

I also tried:

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

And finally:

 email = "john@test.com"
 Email.find(:all, :conditions => ["to_hash['name'] = ?", email]) 
 # 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')

---

The simple answer is;

if you need to query something, you shouldn't serialize it.

Saying that, I think the answer is just

Email.all(:conditions => ["to_hash LIKE '%email: ?%'", "john@test.com"])

If you look at the database contents this should satisfy you.

But going forward you should look for a better solution.

Serialization is great for storing structured data that you never need to use in a sql query, but just gets in the way if you do.

If you really need this kind of freeform data structure, I suggest you look at using MongoDB and Mongoid.

However, within the usual Rails world, I'd suggest the following;

class Email
  has_many :email_recipients

  def to_hash
    email_recipients.map do |recipient|
      {"name" => recipient.name, "email" => recipient.email}
    end
  end
end

class EmailRecipient
  # with columns
  #   email_id
  #   name
  #   email

  belongs_to :email
end

---

One possible way to do this with just regular Ruby is to use the select method and let ActiveRecord take care of deserialization.

emale = "john@test.com"
Email.find(:all).select { |m| m[:to_hash]["email"] === emale }

Another possible solution is to serialize the search hash and match the serialized hash exactly how it is saved in the database. This requires that the hash has all attributes, not just the e-mail. Some useful links to the code that makes this happen available here. You'll see that ActiveRecord uses YAML for serialization by default, so something like this could work.

search_hash = {"name" => "john", "email" => "john@test.com"}
encoder = ActiveRecord::Coders::YAMLColumn.new(Hash)
search_string = encoder.dump(search_hash)
Email.find(:all, :conditions => ["to_hash = ?", search_string])