Shadow translate

, _(*1)

This plugin uses a shadow table for translated content, instead of the core's more flexible (but also potentially quite inefficient) EAV-style translate behavior. The shadow translate behavior is designed to have the same API as the core's translate behavior making it a drop-in replacement in terms of usage., _(*2)

Quickstart

First install the plugin for your app using composer:, _(*3)

php composer.phar require ad7six/shadow-translate, _(*4)

Load the plugin by adding following statement to your app's config/bootstrap.php:, _(*5)

Plugin::load('ShadowTranslate');

The shadow translate behavior expects each table to have its own translation table. Taking the blog tutorial as a start point, the following table would already exist:, _(*6)

CREATE TABLE posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(50),
    body TEXT,
    created DATETIME DEFAULT NULL,
    modified DATETIME DEFAULT NULL
);

To prepare for using the shadow translate behavior, the following table would be created:, _(*7)

CREATE TABLE posts_translations (
    id INT UNSIGNED,
    locale VARCHAR(5),
    title VARCHAR(50),
    body TEXT,
    PRIMARY KEY (id, locale)
);

Note that the id is the same type as the posts table - but the primary key is a compound key using both id and locale., _(*8)

Usage is very similar to the core's behavior so e.g.:, _(*9)

class PostsTable extends Table {

    public function initialize(array $config) {
        $this->addBehavior('ShadowTranslate.ShadowTranslate');
    }
}

You can specify the fields in the translation table - but if you don't they are derived from the translation table schema. From this point forward, see the documentation for the core translate behavior, the shadow translate behavior should act the same, and if it doesn't, well, see below., _(*10)

Why use Shadow Translate

The standard translate behavior uses an EAV style translations table, and one join per field. By default all translations are stored in the same translation table. To give an example, the core translation behavior generates sql of the form:, _(*11)

SELECT
    posts.*,
    posts_title_translations.title,
    posts_title_translations.content,
    etc.
FROM
    posts
LEFT JOIN
    i18n as posts_title_translations ON (
        posts_title_translations.locale = "xx" AND
        posts_title_translations.model = "Posts" AND
        posts_title_translations.foreign_key = posts.id AND
        posts_title_translations.field = 'title'
   )
LEFT JOIN
    i18n as posts_body_translations ON (
        posts_body_translations.locale = "xx" AND
        posts_body_translations.model = "Posts" AND
        posts_body_translations.foreign_key = posts.id AND
        posts_body_translations.field = 'body'
   )
etc.

There is very little setup for the core translate behavior, but the cost for no-setup is sql complexity, and it is more complex for each translated field. Depending on how much data there is being translated - it's quite possible for this data structure to cause slow queries; it also complicates finding records by translated field values., _(*12)

Key points:, _(*13)

Easy to setup
All translated content stored in the same table
One join per translated field when querying
Less efficient queries - more joins and one index for all content
Harder to find by translated content

By contrast, the shadow translate behavior does not use an EAV style translation table, the translations are stored in a copy of the main data table. This permits much less complex sql at the cost of having some setup steps per table. The shadow translate behavior generates sql of the form:, _(*14)

SELECT
    posts.*,
    posts_translations.*
FROM
    posts
LEFT JOIN
    posts_translations ON (
        posts_translations.locale = "xx" AND
        posts_translations.id = posts.id
)
// no etc.

Key points:, _(*15)

(Slightly) more work to setup
Translated content is stored in a copy of the main data table
One join per translated table
More efficient queries - less joins and indexes per translated field are possible
Easier to find by translated content

Roadmap

The initial release is only the behavior, it is planned for the future to add:, _(*16)

A shell to create shadow tables (migration based)
A shell to import from the core's i18n table
A shell to export to the core's i18n table

Bugs

Most likely!, _(*17)

If you happen to stumble upon a bug, please feel free to create a pull request with a fix (optionally with a test), and a description of the bug and how it was resolved., _(*18)

You can also create an issue with a description to raise awareness of the bug., _(*19)