Postgres Backends (Column Attribute)

There are three PostgreSQL-specific backends which use a single column to store translations for a translated attribute: Json (requires Mobility >= 0.5), Jsonb and Hstore. Although the storage formats have many differences, the Mobility implementations are very similar, since in general we will be using them to store depth-1 strings (the Jsonb backend also supports storing other data formats such as integers, hashes, etc, with the same interface, see caveats below). Nonetheless, Jsonb (or Json) would be the preferred choice over Hstore unless you have legacy constraints that require Hstore.

Note: The Json backend has been reported to work with recent versions of MySQL that support JSON column storage, see this issue. However, this backend/db combination is not currently tested so YMMV.

Setup

To use either of these backends, you will need to first add a column on your model for the translated attribute.

Rails/ActiveRecord

There is no Mobility generator for this since the normal migration generator works fine. So to add translated attributes title and content to a model Post, you would use:

rails generate migration AddTitleAndContentToPosts title:jsonb content:jsonb

(For "json", just replace "jsonb" with "json"; for hstore, just replace "jsonb" with "hstore".)

Once the migration is created, make sure to change the actual migration to set the default value of the column(s) to an empty json object ({}) if the column is json/jsonb, or an empty string ('') if the column is hstore.

So for jsonb, it would look like this:

def change
  add_column :posts, :title, :jsonb, default: {}
  add_column :posts, :content, :jsonb, default: {}
end

If the column were an hstore column, it would look like this:

def change
  add_column :posts, :title, :hstore, default: ''
  add_column :posts, :content, :hstore, default:''
end

Then run the migration with rake db:migrate to add the column(s) to your model table.

You may want to consider using a column with a prefix and/or suffix added to the attribute name, which may resolve compatibility issues with gems such as SimpleForm (see #91 for an example). In this case, simply replace the attributes (title and content above) with the prefixed/suffixed name, e.g. title_i18n and content_i18n for a suffix of _i18n, and use the column_prefix and/or column_suffix options described below.

Using the backend is then as simple as setting your default backend to :jsonb (or :json or :hstore), and defining the attributes on the model:

class Post < ApplicationRecord
  extend Mobility
  translates :title, :content
end

To add a prefix/suffix to the attribute name when generating the column name (as described earlier), use the column_prefix/column_suffix options:

class Post < ApplicationRecord
  extend Mobility
  translates :title, :content, column_suffix: '_i18n'
end

Translated attributes title and content are now available. Behind the scenes, when you store values in different locales, Mobility saves these to a single hash for the attribute. You can see the hash using read_attribute:

post = Post.create(title: "foo")
post.title
#=> "foo"
Mobility.with_locale(:ja) { post.title = "あああ" }
post.save
post = Post.first
post[:title]
#=> {"en"=>"foo", "ja"=>"あああ"}
# Or if you used a suffix:
# post[:title_i18n]

Mobility also provides querying shortcuts (as with other backends), so you can easily query by translated attribute using the i18n scope (or i18n dataset for Sequel users):

Post.i18n.where(title: "foo").to_sql
#=> SELECT "posts".* FROM "posts" WHERE (("posts"."title" -> 'en') = "foo")

As with other backends, if you would prefer to directly extend the query methods on your model, you can set the default scope to i18n:

class Post < ApplicationRecord
  translates :title, :content
  default_scope { i18n }
end

Sequel

Mostly the same as above, except that there are no generators. Also, note that for Sequel you need to explicitly enable the pg_json or pg_hstore extensions before you can use jsonb/hstore columns.

If DB is your database instance, you can do this with:

DB.extension :pg_json

for json/jsonb, or

DB.extension :pg_hstore

for hstore. If you do not do this, you will get errors of the form: The AND operator requires at least 1 argument.

Caveats

Querying array-valued translations on a jsonb column

Mobility allows you to translate any data type on a json or jsonb column. Thus it is possible to, say, store and translate arrays of data:

post.title = [1, 2, 3]
Mobility.with_locale(:ja) { post.title = [4, 5, 6] }
post.save
post["title"]
#=> {"en"=>[1,2,3], "ja"=>[4,5,6]}
post.title_en
#=> [1, 2, 3]
post.title_ja
#=> [4, 5, 6]

Beware however that when querying on arrays of values, Mobility will interpret them not as an array but as a set of values to match, e.g.:

Post.i18n.find_by(title: ["foo", "bar"])

will look for posts with a title that is either "foo" or "bar", rather than a post with the array-valued title ["foo", "bar"]. See this issue for background on this.

To get around this, wrap the array in another array:

Post.i18n.find_by(title: [["foo", "bar"]])

This will generate the desired query:

SELECT "posts".* FROM "posts" WHERE (("posts"."translations" -> 'en') -> 'title') = '["foo","bar"]

(Querying on non-string data types is not supported for columns with the json type.)