Rails - Oct 2011 - 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-J0of1frlU80@public.gmane.org" 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-J0of1frlU80@public.gmane.org"}
or an array of hashes

 :to_hash = [ {"name" => "john", "email" =>
"john-J0of1frlU80@public.gmane.org"}, {"name"
=> "bob", "email" =>
"bob-hcDgGtZH8xNBDgjK7y7TUQ@public.gmane.org"} ]
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-J0of1frlU80@public.gmane.org"}, to_hash:
[{"name"=>"michie",
"email"=>"mich-7Ts6kVb0ZJk@public.gmane.org"}, 
{"name"=>"clarisa",
"email"=>"clarisa-M/39claqbm0@public.gmane.org"}],
cc_hash: [{"name"=>"john",
 "email"=>"john-J0of1frlU80@public.gmane.org"},
{"name"=>"alex",
"email"=>"alex-abbBDnLRM0pBDgjK7y7TUQ@public.gmane.org"}],
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-J0of1frlU80@public.gmane.org"}
 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-J0of1frlU80@public.gmane.org
 '')
I also tried:

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

 emale = "john-J0of1frlU80@public.gmane.org"
 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-J0of1frlU80@public.gmane.org'')

-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 3 October 2011 02:57, Rodrigo
<joserodrigofuentes-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> I want to find all records in a model that contains the email
> "john-J0of1frlU80@public.gmane.org" 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.

-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
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.

On Oct 2, 9:57 pm, Rodrigo
<joserodrigofuen...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> I want to find all records in a model that contains the email
> "j...-J0of1frlU80@public.gmane.org" 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
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-J0of1frlU80@public.gmane.org''
end

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

--Matt Jones

-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
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.