How do I serialize an array field into MySQL?

Question

I have request_type as a array field in my database. For this I did following migration

def change
    change_column :users, :request_type, :string, array: true, default: '{}'
end

which I got from this question.

I also added serialize into the model file.

class User < ActiveRecord::Base
  belongs_to :client
  serialize :request_type, Array
  self.per_page = 15
end

A user can perform many requests, which I'm trying to store on their record in this format:

request_type: {"Discount", "Offer", "other options"}

I also tried to update them from console but it doesn't work there either.

a.request_type.push("offer")

getting NoMethodError: undefined method push' for "[]":String error.

When I try to add an item

a.request_type << "Offer"

the value becomes "[]Offer"

I know it is storing as string, so how can I update this field?

I am using rails 4, ruby 2.2.2, mysql.

Edit Stack trace

2.2.2 :016 > a = User.last
  User Load (0.4ms)  SELECT  `users`.* FROM `users`  ORDER BY `users`.`id` DESC LIMIT 1
 => #<User id: 13, name: nil, email: nil, address: nil, mobile_number: "1234567890", client_id: 27, selected_option: nil, created_at: "2015-08-10 09:46:11", updated_at: "2015-08-10 09:46:11", ad_code: "1000014", request_type: "[]", sid: nil, circle: nil> 
2.2.2 :017 > a.request_type << "offer"
 => "[]offer" 
2.2.2 :018 > a.request_type.push("offer")
NoMethodError: undefined method `push' for "[]offer":String
2.2.2 :008 > a.request_type << "offer".to_yaml
 => "[]--- offer\n...\n" 
2.2.2 :011 > a.request_type << "mnb".to_yaml
 => "[]--- offer\n...\n--- mnb\n...\n"

Have you tried just `a.request_type = ["Discount", "Offer"]` and `a.save` ? Also the `string` type of column not efficient, [because it's `255` length use `text` instead.](http://stackoverflow.com/a/3354452/2057388) — Roman Kiselenko, Aug 10 '15 at 09:52
yup, but getting ActiveRecord::SerializationTypeMismatch: Attribute was supposed to be a Array, but was a String. -- "[]" error — shrikant1712, Aug 10 '15 at 09:58
try this `a.request_type = ["Discount", "Offer"].to_yaml` - [_The serialization is done through YAML._](http://api.rubyonrails.org/classes/ActiveRecord/AttributeMethods/Serialization/ClassMethods.html#method-i-serialize) but in Rails 4 it's should work without `.to_yaml` — Roman Kiselenko, Aug 10 '15 at 10:00
also i suggest you try just `serialize :request_type` i use it for now without a second option. — Roman Kiselenko, Aug 10 '15 at 10:06
I tried your solutions but when I try to push another "xyz" into that `a.request_type` getting the issue. — shrikant1712, Aug 10 '15 at 10:09
according my suggestion, you are can't just `push(<<)` now, is only a one way to use it with `to_yaml` it's alway use it like `a.request_type = ["Discount", "Offer", "New value"].to_yaml`. — Roman Kiselenko, Aug 10 '15 at 10:11
Please show the result of `a.request_type = ["Discount"].to_yaml` __not__ `a.request_type << ["Discount"].to_yaml` nor `a.request_type << "offer".to_yaml` — Roman Kiselenko, Aug 10 '15 at 10:19
I think serialize expects the database field to be `text` or `string` (text is safer). Try changing the column type to be a text field, without the `array` option. — Max Williams, Aug 10 '15 at 10:41

score 5 · Answer 1 · answered Oct 18 '15 at 17:55

Rails 4 with MySql serializes Arrays as YAML, but you've set the default to be '{}', which can't be deserialized properly and remains a string. When I tested that way, I got the same result you did. Instead, in your migration, use default: [] and let Rails will set the column default to the appropriately serialized YAML version.

This was tested in Rails 4.0.13, Ruby 2.2.2, MySQL 5.6.13:

class AddThings < ActiveRecord::Migration
  def change
    create_table :things do |t|
      t.string :items, array: true, default: []
    end
  end
end

class Thing < ActiveRecord::Base
  serialize :items
end

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| items | varchar(255) | YES  |     | --- []  |                |
+-------+--------------+------+-----+---------+----------------+

> t = Thing.new
 => #<Thing id: nil, items: []>
> t.items << 'a'
 => ["a"]
> t.save
  SQL (0.3ms)  INSERT INTO `things` (`items`) VALUES ('---\n- a\n')
> t = Thing.last
 => #<Thing id: 1, items: ["a"]>
> t.items = %w[a b c]
 => ["a", "b", "c"]
> t.save
  SQL (0.3ms)  UPDATE `things` SET `items` = '---\n- a\n- b\n- c\n' WHERE `things`.`id` = 1
 => true
> t.reload.items
 => ["a", "b", "c"]

How do I serialize an array field into MySQL?

1 Answers1