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
[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 = '—

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]’)

On 3 October 2011 02:57, Rodrigo [email protected] wrote:

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?

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.

On Oct 2, 9:57pm, Rodrigo [email protected] wrote:

t.string :gmailMessageId
serialize :from_hash, Hash
serialize :to_hash, Array
serialize :cc_hash, Array

end

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, ‘[email protected]
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 J.