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",
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"].

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

--
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@googlegroups.com.
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.

Search Discussions

  • Michael Pavling at Oct 3, 2011 at 5:39 am

    On 3 October 2011 02:57, Rodrigo wrote:
    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.

    --
    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@googlegroups.com.
    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.
  • Matt Jones at Oct 3, 2011 at 9:31 am

    On Oct 2, 9:57 pm, Rodrigo wrote:
    I want to find all records in a model that contains the email
    "j...@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
    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. :)

    --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@googlegroups.com.
    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouprubyonrails-talk @
categoriesrubyonrails
postedOct 3, '11 at 1:57a
activeOct 3, '11 at 9:31a
posts3
users3
websiterubyonrails.org
irc#RubyOnRails

People

Translate

site design / logo © 2021 Grokbase