Storing arrays in database using ActiveRecord

Question

I am on rails 2.3.8 & I am using mysql as db adapter. I want to store arrays in my database. After searching I could come up with this very useful article.

Now I need to use GUI for input & not only server console. So say I have a text field called nums which logically should have int array. What should be the format of nums so that it becomes easy to retrieve & store the array out of that string ?

In Rails 4, you can now use the array type, which stores it as an array on PostgreSQL, or as a string on everything else. — Zaz
– Zaz, Commented Jul 30, 2014 at 18:55

Mike Woodhouse · Accepted Answer · 2010-06-23 10:01:45Z

37

If you use serialize then you shouldn't have to worry about how the data is stored within the text field, although it's actually YAML.

serialize is documented in the Rails/ActiveRecord API (scroll down to the section headed "Saving arrays, hashes, and other non-mappable objects in text columns")

For display, you need a format that is understandable to users and that can be easily converted back into an array in your code. Comma- or space-delimited?

Formatting for output:

delim = ',' # or ' ' for spaces, or whatever you choose
array.join(delim)

Converting back into an array might work as follows:

num_array = nums.split(delim).map(&:to_i) # or to_f if not integers

or perhaps using String#scan?

num_array = nums.scan(/\d+/).map(&:to_i) # for positive integers

answered Jun 23, 2010 at 10:01

Mike Woodhouse

52.3k12 gold badges90 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nikhil Garg Over a year ago

Thanks Mike. :) I had already figured it out. One still easier (though error prone) way is directly to use eval function. so : num_array=eval(nums) work sweet too !

MunkiPhD Over a year ago

It's easier, but if you're going to use eval, you need to scrub the data for invalid inputs VERY thoroughly. eval() opens a ton of security holes if you're not careful.

Steven Barragán · Accepted Answer · 2015-03-05 18:16:26Z

If you're using postgres and rails 4, now you have a better native option.

# db/migrate/20140207133952_create_books.rb
create_table :books do |t|
  t.string 'title'
  t.string 'tags', array: true
  t.integer 'ratings', array: true
end
add_index :books, :tags, using: 'gin'
add_index :books, :ratings, using: 'gin'

# app/models/book.rb
class Book < ActiveRecord::Base
end

# Usage
Book.create title: "Brave New World",
            tags: ["fantasy", "fiction"],
            ratings: [4, 5]

## Books for a single tag
Book.where("'fantasy' = ANY (tags)")

## Books for multiple tags
Book.where("tags @> ARRAY[?]::varchar[]", ["fantasy", "fiction"])

## Books with 3 or more ratings
Book.where("array_length(ratings, 1) >= 3")

http://edgeguides.rubyonrails.org/active_record_postgresql.html

Collectives™ on Stack Overflow

Storing arrays in database using ActiveRecord

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related