Question

I am building a database of online offers. Each offer can be viewed in one or more countries, for instance an advertiser may be interested in reaching offers in the US & Canada.

The list of countries we are covering is about 50 long with each country identified with an ISO standard two letter acronym like US, CA, GB, FR, DE, etc.

I could handle this with a HABTM table but it seems inefficient to have a long table joining offers with countries when I could simply use the SET column type in MySQL & Postgres (see MySQL documentation on SET TYPE here).

I did try adding a column of type set as follows:

  t.set :regions, :limit => ["GB","FR","DE"]

This throws no error but the field regions is now missing from my object. I can t find much on Google as the word set is the most commonly re-used word in the English language (140+ different uses and 6 pages in the dictionary). The only relevant article was on Rails Wiki from 2006 that is now 404-ing.

Would appreciate any pointers and will implement a habtm in the meantime.

Answer 1

I ended up overriding the default accessor methods in my model as follows:

def regions=(list)
  write_attribute(:regions, pack(list))
end

def regions
  read_attribute(:regions).split("::")
end

private
def pack(var)
  field = var[0]
  list = var.dup
  list.shift
  list.each do |i|
    field = [field,i].join("::")
  end
  field
end

I set the regions field to type text . This code stores the list with "::" as a separator.

友情链接