I'm trying to store a Hash of arrays using Rails 4 and Hstore (Postgres 9.2) for when users submit a form with a bunch of checkboxes. The problem I'm having is that once they're stored, all the values get quotes escaped and it's unreadable when retrieved
irb(main):070:0> Plan
=> Plan(id: integer, email: string, created_at: datetime, updated_at: datetime, first_name: string, professional: boolean, preferences: hstore)
irb(main):070:0> params = {"plan"=>{"first_name"=>"nick", "email"=>"test@test.com", "professional"= z", "gak"], "too"=>["tar", "taz", "tak"], :preferences=>{"foo"=>["", "bar", "baz", "bak"], "goo"=>["", "gar", "gaz", "gak"], "too"=>["tar", "taz", "tak"]}}}
=> {"plan"=>{"first_name"=>"nick", "email"=>"test@test.com", "professional"=>"true"}, "preferences"=>{"foo"=>["", "bar", "baz", "bak"], "goo"=>["", "gar", "gaz", "gak"], "too"=>["tar", "taz", "tak"], :preferences=>{"foo"=>["", "bar", "baz", "bak"], "goo"=>["", "gar", "gaz", "gak"], "too"=>["tar", "taz", "tak"]}}}
irb(main):071:0> plan = Plan.new(params['plan']) => #<Plan id: nil, email: "test@test.com", created_at: nil, updated_at: nil, first_name: "nick", professional: true, preferences: nil>
irb(main):072:0> plan.preferences = params['preferences']
=> {"foo"=>["", "bar", "baz", "bak"], "goo"=>["", "gar", "gaz", "gak"], "too"=>["tar", "taz", "tak"], :preferences=>{"foo"=>["", "bar", "baz", "bak"], "goo"=>["", "gar", "gaz", "gak"], "too"=>["tar", "taz", "tak"]}}
irb(main):073:0> plan
=> #<Plan id: nil, email: "test@test.com", created_at: nil, updated_at: nil, first_name: "nick", professional: true, preferences: {"foo"=>["", "bar", "baz", "bak"], "goo"=>["", "gar", "gaz", "gak"], "too"=>["tar", "taz", "tak"], :preferences=>{"foo"=>["", "bar", "baz", "bak"], "goo"=>["", "gar", "gaz", "gak"], "too"=>["tar", "taz", "tak"]}}>
irb(main):074:0> plan.save
(0.2ms) BEGIN
SQL (1.2ms) INSERT INTO "plans" ("created_at", "email", "first_name", "preferences", "professional", "updated_at") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id" [["created_at", Mon, 18 Nov 2013 15:44:46 UTC +00:00], ["email", "test@test.com"], ["first_name", "nick"], ["preferences", {"foo"=>["", "bar", "baz", "bak"], "goo"=>["", "gar", "gaz", "gak"], "too"=>["tar", "taz", "tak"], :preferences=>{"foo"=>["", "bar", "baz", "bak"], "goo"=>["", "gar", "gaz", "gak"], "too"=>["tar", "taz", "tak"]}}], ["professional", true], ["updated_at", Mon, 18 Nov 2013 15:44:46 UTC +00:00]]
(2.4ms) COMMIT
=> true
irb(main):075:0> plan
=> #<Plan id: 27, email: "test@test.com", created_at: "2013-11-18 15:44:46", updated_at: "2013-11-18 15:44:46", first_name: "nick", professional: true, preferences: {"foo"=>["", "bar", "baz", "bak"], "goo"=>["", "gar", "gaz", "gak"], "too"=>["tar", "taz", "tak"], :preferences=>{"foo"=>["", "bar", "baz", "bak"], "goo"=>["", "gar", "gaz", "gak"], "too"=>["tar", "taz", "tak"]}}>
irb(main):076:0> plan_out = Plan.find(27)
Plan Load (0.7ms) SELECT "plans".* FROM "plans" WHERE "plans"."id" = $1 LIMIT 1 [["id", 27]]
=> #<Plan id: 27, email: "test@test.com", created_at: "2013-11-18 15:44:46", updated_at: "2013-11-18 15:44:46", first_name: "nick", professional: true, preferences: {"foo"=>"[\"\", \"bar\", \"baz\", \"bak\"]", "goo"=>"[\"\", \"gar\", \"gaz\", \"gak\"]", "too"=>"[\"tar\", \"taz\", \"tak\"]", "preferences"=>"{\"foo\"=>[\"\", \"bar\", \"baz\", \"bak\"], \"goo\"=>[\"\", \"gar\", \"gaz\", \"gak\"], \"too\"=>[\"tar\", \"taz\", \"tak\"]}"}>
irb(main):077:0> plan.preferences
=> {"foo"=>["", "bar", "baz", "bak"], "goo"=>["", "gar", "gaz", "gak"], "too"=>["tar", "taz", "tak"], :preferences=>{"foo"=>["", "bar", "baz", "bak"], "goo"=>["", "gar", "gaz", "gak"], "too"=>["tar", "taz", "tak"]}}
irb(main):083:0> plan_out.preferences
=> {"foo"=>"[\"\", \"bar\", \"baz\", \"bak\"]", "goo"=>"[\"\", \"gar\", \"gaz\", \"gak\"]", "too"=>"[\"tar\", \"taz\", \"tak\"]", "preferences"=>"{\"foo\"=>[\"\", \"bar\", \"baz\", \"bak\"], \"goo\"=>[\"\", \"gar\", \"gaz\", \"gak\"], \"too\"=>[\"tar\", \"taz\", \"tak\"]}"}
irb(main):085:0> plan_out.preferences['foo']
=> "[\"\", \"bar\", \"baz\", \"bak\"]"
irb(main):086:0> plan_out.preferences['foo'].first
=> "["
So you can see that the plan is fine in it's instance, but when retrieved from the db, the hstore it's escaped and comes out as a string. My Plan model looks like this:
class Plan < ActiveRecord::Base
store_accessor :preferences, :foo, :goo, :too
validates :email, presence:true, email_format:true
validates :professional, :inclusion => {:in => [true, false], :message => "? - Please choose 'Yes' or 'No' for Nutrition Professional"}
end
Any insight would help. Am I trying to do something that Hstore can't do? I'd hate to have to do the full relational db for these records, it seems like a good use for serializing.
Thanks!
From what I know, HSTORE values are just plain strings.
This is what the documentation says: "Keys and values are simply text strings."
So you might convert the values yourself.
Another possibility would be to use the super nice JSON features of Postgres. Those should allow native array storing.