Table Backend

Chris Salzberg edited this page Sep 11, 2018 · 15 revisions

The Table backend stores translation in a model-specific table. For a model Post with a table posts, the translation table would be post_translations. The translation table has columns for every translated attribute, as well as a column for the locale and a foreign key pointing to the model table (post_id).

A detailed description of this backend is provided as "Strategy #2" in this blog post.

Generating the Translation Table

Unlike the default KeyValue backend, for the Table backend you will need to generate and run a migration for each translated model. Mobility has a built-in generator for this. If your default backend is set to :table, and you want to translate a model Post to add translated columns title (string) and content (text), here is how you would do it:

rails generate mobility:translations post title:string content:text

(Pass --backend=table as an option if your default backend is something else.)

This will generate a migration to create the following table:

create_table "post_translations", force: :cascade do |t|
  t.string   "title"
  t.text     "content"
  t.string   "locale",     null: false
  t.integer  "post_id",    null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.index ["locale"], name: "index_post_translations_on_locale"
  t.index ["post_id", "locale"], name: "index_post_translations_on_post_id_and_locale", unique: true

As can be seen, the translations table has two attribute columns, title and content, as well as a locale column, timestamps, and a reference to the post (post_id), as well as some indices to speed up translation retrieval. Note that the index on post_id and locale is unique, since normally you would never have more than one translation for a given locale on a given record.

If you think you will be searching on a translated attribute, you can pass index after the type specification for the column when generating translations and Mobility will add an index for the column, like this:

rails generate mobility:translations post title:string:index content:text:index

This will generate an index on the translation table "title" and "content" columns.

Also, note that if you later need to add new columns to an existing translations table, you can re-run the generator with the new attribute names and Mobility will create an appropriate migration to add missing columns (leaving already-defined columns as-is).


In order to manage translations on a model, Mobility subclasses the Mobility::ActiveRecord::ModelTranslation (or Mobility::Sequel::ModelTranslation for Sequel) abstract class and assigns it to a class with a name Post::Translation (for a model Post). It then defines two associations: a has_many association from a model to its translations, and an inverse belongs_to association from the translation class back to the model, named translated_model.

The result looks something like this:

class Post < ApplicationRecord
  has_many :translations,
    class_name:  Post::Translation,
    foreign_key: :post_id,
    dependent:   :destroy,
    autosave:    true,
    inverse_of:  :translated_model
  # ...


class Post::Translation < ApplicationRecord
  belongs_to :post,
    class_name:  Post,
    foreign_key: :post_id,
    inverse_of:  :translations,
    touch: true

  # ...

When you get a value with post.title, the backend does roughly the following to get the value:

locale = Mobility.locale
translation = translations.find { |t| t.locale == locale.to_s }
translation ||= locale)

So, Mobility looks for a matching translation, and if one does not exist, it builds on with the current locale. This is very similar to how the KeyValue backend fetches translations on a polymorphic association.


Queries with the table backend can be somewhat complex since Mobility needs to join any translation tables involved. If we query on the title column above, like this:

Post.i18n.find_by(title: "foo")

ActiveRecord (or Sequel) will generate SQL like this:

SELECT "posts".* FROM "posts"
  INNER JOIN "post_translations" "post_translations_en"
  ON "post_translations_en"."post_id" = "posts"."id" AND "post_translations_en"."locale" = 'en'
  WHERE "post_translations_en"."title" = 'foo'

So, we're joining the post_translations table on translations whose post_id matches the post id, and also on locales matching the current Mobility locale, which in this case is en. With the join, we can then query on the post_translations.title column.

Notice that the join here is aliased to post_translations_en, since we are querying in the English locale. This is important and different from how other translation gems (i.e. Globalize) handle joining translations. By aliasing to a name which includes the locale, Mobility makes it possible to perform complex queries on multiple locales at once, like this:

Post.i18n.where(title: "foo", locale: :ja).where(title: "bar", locale: :en)

This is querying for posts whose title is "foo" in Japanese and "bar" in English. This becomes:

SELECT "posts".* FROM "posts"
  INNER JOIN "post_translations" "post_translations_ja"
  ON "post_translations_ja"."post_id" = "posts"."id" AND "post_translations_ja"."locale" = 'ja'
  INNER JOIN "post_translations" "post_translations_en" 
  ON "post_translations_en"."post_id" = "posts"."id" AND "post_translations_en"."locale" = 'en'
  WHERE "post_translations_ja"."title" = 'foo' AND "post_translations_en"."title" = 'bar'

This can be very useful if, for example, you need to narrow down a search to only match translations from one value in one locale to another value in another locale.

Although somewhat complex, Table backend queries are not nearly as complex as queries in the (default) KeyValue backend, where we must alias columns and do other tricks. This is the cost of setup: with the Table backend, we need a migration for every translated model (and new migrations whenever we add new translated attributes to an existing translation table). For the KeyValue backend, which in other ways is quite similar, we never need to migrate since everything is stored in one table. You should decide which backend to use based on your needs and (in this case specifically) how complex you imagine your querying needs will be.

You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.