KeyValue Backend

The KeyValue backend stores translations on a set of two shared tables, one for string-valued translations and one for text-valued translations. These tables are generated when you run the default install generator (rails generate mobility:install) and then migrate your database (rake db:migrate).

A detailed description of the backend is described as "Strategy #3" in this blog post.

Table Structure

The tables generated by the default migration look like this:

create_table "mobility_string_translations", force: :cascade do |t|
  t.string   "locale"
  t.string   "key"
  t.string   "value"
  t.integer  "translatable_id"
  t.string   "translatable_type"
  t.datetime "created_at",        null: false
  t.datetime "updated_at",        null: false
  t.index ["translatable_id", "translatable_type", "key"], name: "index_mobility_string_translations_on_translatable_attribute"
  t.index ["translatable_id", "translatable_type", "locale", "key"], name: "index_mobility_string_translations_on_keys", unique: true
  t.index ["translatable_type", "key", "value", "locale"], name: "index_mobility_string_translations_on_query_keys"
end

create_table "mobility_text_translations", force: :cascade do |t|
  t.string   "locale"
  t.string   "key"
  t.text     "value"
  t.integer  "translatable_id"
  t.string   "translatable_type"
  t.datetime "created_at",        null: false
  t.datetime "updated_at",        null: false
  t.index ["translatable_id", "translatable_type", "key"], name: "index_mobility_text_translations_on_translatable_attribute"
  t.index ["translatable_id", "translatable_type", "locale", "key"], name: "index_mobility_text_translations_on_keys", unique: true
end

Both tables have a translatable_type string column, because these will be used in a polymorphic relationship with translated models.

To understand how this works, suppose we have a model Post:

class Post < ApplicationRecord
  extend Mobility
  translates :title,   backend: :key_value, type: :string
  translates :content, backend: :key_value, type: :text
end

The backend will create associations on the class roughly resulting in:

class Post < ApplicationRecord
  has_many :mobility_string_translations, ->{ where key: [:title] },
    as:         :translatable,
    class_name: Mobility::ActiveRecord::StringTranslation,
    dependent:  :destroy,
    inverse_of: :translatable,
    autosave:   true

  has_many :mobility_text_translations,   ->{ where key: [:content] },
    as:         :translatable,
    class_name: Mobility::ActiveRecord::TextTranslation,
    dependent:  :destroy,
    inverse_of: :translatable,
    autosave:   true
end

So the backend adds associations for each type, scoped to only include translations whose keys are :title and :content, respectively.

The classes Mobility::ActiveRecord::StringTranslation and Mobility::ActiveRecord::TextTranslation both inherit from Mobility::ActiveRecord::Translation, which is an abstract class:

module Mobility
  module ActiveRecord
    class Translation < ::ActiveRecord::Base
      self.abstract_class = true

      belongs_to :translatable, polymorphic: true

      validates :key, presence: true, uniqueness: { scope: [:translatable_id, :translatable_type, :locale] }
      validates :translatable, presence: true
      validates :locale, presence: true
    end
  end
end

Since the class itself is abstract, it has no table; the table is set in the descendants to mobility_string_translations and mobility_text_translations, respectively. The different tables is actually the only difference between the string and text translation classes, and the only difference between the tables is that their value columns are of different types (string/text) (and that the string table has one additional index on value).

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

locale = Mobility.locale
translation = mobility_string_translations.find { |t| t.key == "title" && t.locale == locale.to_s }
translation ||= translations.build(locale: locale, key: "title")
translation.value

So it finds the translation from all translations associated with the model which has a key "title" and a locale equal to Mobility.locale. If such a translation does not exist, it builds one. It then returns the value. Setting is much the same, with the value of the translation updated to a new value.

Querying

Querying on the KeyValue backend is quite complex since the associations above are polymorphic. Suppose we have a model, Post:

class Post < ApplicationRecord
  extend Mobility
  translates :title, backend: :key_value, type: :text
end

If we now add the Mobility scope (i18n) and query for posts with a title matching the string "foo", like this:

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

This is the resulting SQL:

SELECT "posts".* FROM "posts"
INNER JOIN "mobility_text_translations" "title_text_translations"
  ON "title_text_translations"."key" = 'title
  AND "title_text_translations"."locale" = 'en'
  AND "title_text_translations"."translatable_type" = 'Post'
  AND "title_text_translations"."translatable_id" = "posts"."id"
WHERE "title_text_translations"."value" = 'foo'

As you can see, Mobility executes an INNER JOIN on a set of columns on mobility_text_translations (since this attribute is of type "text"), two of which match the Model name ('Post') and id "posts"."id", and the others matching the current locale ("en") and attribute name ("title"), and finally with the where condition matching the attribute value ("title_text_translations"."value" = 'foo').

The key trick here is the alias of "mobility_text_translations" to "title_text_translations". This is required because if we query on multiple translated (key-value) attributes, we need to manage these joins and query conditions separately. So if you query on a few different translated attributes, you may end up with a some complex-looking SQL in your logs.

In general, this SQL should not be an issue. However, one thing to keep in mind is that by default, the results returned by Post.i18n.where(title: "foo") will not be eager loaded. This has nothing really to do with the query structure, but instead results from the polymorphic associations described above.

If you want to eagerly load results (to avoid "N+1" queries when e.g. listing a set of query results), you can overcome this problem by explicitly eager loading the associations you will need. For example, if you are querying for text-valued key-value attributes, you can tack eager_load(:text_translations) onto your query, like this:

Post.i18n.where(title: "foo").eager_looad(:text_translations)

This will result in a query which selects all the columns you need from the polymorphic text_translations association, like this:

SELECT "posts"."id" AS t0_r0,
  "posts"."created_at" AS t0_r1,
  "posts"."updated_at" AS t0_r2,
  "text_translations_posts"."id" AS t1_r0,
  "text_translations_posts"."locale" AS t1_r1,
  "text_translations_posts"."key" AS t1_r2,
  "text_translations_posts"."value" AS t1_r3,
  "text_translations_posts"."translatable_id" AS t1_r4,
  "text_translations_posts"."translatable_type" AS t1_r5,
  "text_translations_posts"."created_at" AS t1_r6,
  "text_translations_posts"."updated_at" AS t1_r7
FROM "posts"
LEFT OUTER JOIN "mobility_text_translations" "text_translations_posts"
  ON "text_translations_posts"."translatable_id" = "posts"."id"
  AND "text_translations_posts"."key" = 'title'
  AND "text_translations_posts"."translatable_type" = $1
INNER JOIN "mobility_text_translations" "title_text_translations"
  ON "title_text_translations"."key" = 'title'
  AND "title_text_translations"."locale" = 'en'
  AND "title_text_translations"."translatable_type" = 'Post'
  AND "title_text_translations"."translatable_id" = "posts"."id"
WHERE "title_text_translations"."value" = $2  [["translatable_type", "Post"], ["value", "foo"]]

You can see here that we now have two joins, one to match only posts whose title is "foo", the other one to join (with an OUTER JOIN) all columns on mobility_text_translations. Once you have eager-loaded results this way, you should not see any extra queries when later fetching post.title (or whatever the translated attribute name is).