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

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",
{"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')

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?

You should probably change the array of hashes to an array of AR associations,

The email.rb model file says:

class Email < ActiveRecord::Base

has_many :from_addresses, :class => "EmailAddress"
has_many :to_addresses, :class => "EmailAddress"
has_many :cc_addresses, :class => "EmailAddress"

end

So that the EmailAddress model implements all the keys of your old
hashes as attributes, and you can then use regular finders.

There are two major categories of solutions to this - either normalize
the data to more SQL records, or apply a full-text search system.
Michael's already described the SQL solution, so here's the other one.

You might want to consider using Sunspot (http://outoftime.github.com/
sunspot/ ) for this - it's got the ability to filter data by
multivalued string fields for exactly this sort of situation. The site
will provide you with setup information, but ultimately you'd end up
with a model like this:

class Email < ActiveRecord::Base
  serialize :from_hash, Hash
  serialize :to_hash, Array
  serialize :cc_hash, Array

  searchable do
    string :to_emails, :multiple => true do
      to_hash.map { |x| x['email'] }
    end
    ...etc...
  end
end

Searching is pretty straightforward:

Email.search do
  with :to_emails, 'joe@test.com'
end

This will also be a huge help when the inevitable, "Hey, can we search
email TEXT as well?" request comes in. :slight_smile:

--Matt Jones