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

rubyonrails-talk
1

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')

2

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.

3

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