Chris
Chris White Web Developer

Translating Eloquent Fields with MySQL's Native JSON Type

28 March 2016 ~3 minute read

Since version 5.7.8, MySQL has supported a native JSON data type. Since I’m a bit of a weirdo who finds structured data formats interesting, I wanted to experiment with its different uses in the context of a web application. One potential use-case I thought of was using it for internationalisation – storing different text translations for a field.

Let’s take a look at how internationalisation is typically done in a web application and how it could be done through the use of MySQL’s native JSON type. Since I’m uncreative, I’ll be using Laravel and an age-old blog posts example. We’ll keep it simple and say the requirement is that the titles and body content of our posts need to be multilingual. If you want to jump straight to the proof of concept, here’s the GitHub repository.

The boring, traditional way

Normally we’d achieve this by creating two database tables for our posts. The first table contains only language-neutral data: things like primary keys, fields that are the same across languages, etc. The second table contains the localised text, stored against the relevant ISO code of the language it represents. In a typical Laravel migration class, that might look like this:

1Schema::create('posts', function (Blueprint $table) {
2 $table->increments('id');
3 $table->string('slug')->unique();
4 $table->timestamps();
5});
6 
7Schema::create('post_translations', function (Blueprint $table) {
8 $table->unsignedInteger('post_id');
9 $table->string('locale');
10 $table->string('title');
11 $table->text('content');
12 $table->timestamps();
13 
14 $table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');
15});

When we want to retrieve a post in a specific language, we’d execute a query that joins the post_translations table in order to grab the localised text.

1$post = DB::table('posts')
2 ->select('posts.id', 'posts.slug', 'post_translations.title', 'post_translations.content')
3 ->join('post_translations', 'post_translations.post_id', '=', 'posts.id')
4 ->where('posts.id', 1)
5 ->where('post_translations.locale', 'en') // or 'fr', 'de' etc.
6 ->first();

This is a tried and true method, and arguably the best since it follows database normalisation principles. But it doesn’t use JSON, and any JavaScript developer will tell you that JSON is cool.

The JSON way

We can achieve the same thing using MySQL’s native JSON data type by changing our migration to the following.

1Schema::create('posts', function (Blueprint $table) {
2 $table->increments('id');
3 $table->string('slug')->unique();
4 $table->json('title');
5 $table->json('content');
6 $table->timestamps();
7});

Since we’ll now be storing the different localised text strings in the same table as the object, we can drop the post_translations table. We’ve made the title and content fields take JSON, which will have the following structure:

1{
2 "en": "Hello world",
3 "fr": "Bonjour le monde",
4 "de": "Hallo welt"
5}

To retrieve blog posts in a particular language, our query now changes to take advantage of Laravel 5.2’s support of JSON:

1$post = DB::table('posts')
2 ->select('posts.id', 'posts.slug', 'posts.title->en', 'posts.content->en') // or 'posts.title->fr'
3 ->where('posts.id', 1)
4 ->first();

This is a much simpler query that requires no joins. In the background, Laravel is executing a query taking advantage of MySQL’s native JSON path syntax:

1select `posts`.`id`, `posts`.`slug`, `posts`.title->"$.en", `posts`.content->"$.en" from `posts` where `posts`.`id` = ?

Automating the translations

This is great and all, but it doesn’t help us much in a real application. Laravel’s DB facade returns stdClass objects as results, not our nice Eloquent models. We’ll also likely want to default to a specific language depending on the locale specified in our application’s config, and fall back to another if it’s not available. To help with this, we’ll create a trait that can be used by our Eloquent models to automatically retrieve the correct translation for a model field. Dump the code below into Translatable.php, somewhere in your project.

1<?php
2 
3namespace App;
4 
5trait Translatable
6{
7 /**
8 * Returns a model attribute.
9 *
10 * @param $key
11 * @return string
12 */
13 public function getAttribute($key)
14 {
15 if (isset($this->translatable) && in_array($key, $this->translatable)) {
16 return $this->getTranslatedAttribute($key);
17 }
18 
19 return parent::getAttribute($key);
20 }
21 
22 /**
23 * Returns a translatable model attribute based on the application's locale settings.
24 *
25 * @param $key
26 * @return string
27 */
28 protected function getTranslatedAttribute($key)
29 {
30 $values = $this->getAttributeValue($key);
31 $primaryLocale = config('app.locale');
32 $fallbackLocale = config('app.fallback_locale');
33 
34 if (!$values) {
35 return null;
36 }
37 
38 if (!isset($values[$primaryLocale])) {
39 // We don't have a primary locale value, so return the fallback locale.
40 // Failing that, return an empty string.
41 return $values[$fallbackLocale] ?: '';
42 }
43 
44 return $values[$primaryLocale];
45 }
46 
47 /**
48 * Determine whether the provided attribute should be casted as JSON when it is being set.
49 * If it is a translatable field, it should be casted to JSON.
50 *
51 * @param $key
52 * @return bool
53 */
54 protected function isJsonCastable($key)
55 {
56 if (isset($this->translatable) && in_array($key, $this->translatable)) {
57 return true;
58 }
59 
60 return parent::isJsonCastable($key);
61 }
62}

What we’re doing above is overriding Illuminate\Database\Eloquent\Model's implementation of getAttribute() with our own. The getAttribute() method will be executed on each access to the model’s fields. We’ll check if the field we’re accessing has translations and if it has, we’ll return the correct one based on the locale setting defined in the application’s config. If there’s no entry for that locale, we’ll use the fallback locale, and as a last resort we’ll just return an empty string.

1<?php
2 
3namespace App;
4 
5use Illuminate\Database\Eloquent\Model;
6 
7class Post extends Model
8{
9 use Translatable;
10 
11 protected $table = 'posts';
12 
13 public $translatable = ['title'];
14 public $casts = ['title' => 'json'];
15}

You’ll notice that just useing the trait isn’t enough – we also have to tell the getAttribute() method what model fields are translatable. Also, we have to use the $casts property to let Laravel know that it should save this field as JSON when it persists to MySQL.

Saving or updating a post with translated fields becomes super easy.

1Post::create([
2 'slug' => 'test-post-please-ignore',
3 'title' => [
4 'en' => 'Test post please ignore',
5 'fr' => "post test s'il vous plaît ignorer",
6 'de' => 'Test- Post bitte ignorieren'
7 ],
8 'content' => [
9 'en' => 'I am just a test post',
10 'fr' => 'Je suis juste un post-test',
11 'de' => 'Ich bin nur ein Test Post'
12 ]
13]);

Check out the GitHub repository to see it in a working Laravel application.

Are you convinced?

I’ll leave this one up to you. Personally, I’m not convinced enough in this approach to drop the translation text lookup table for a JSON field. I’m not a fan of having to retrieve and re-save every language’s translation when adding/removing one translation.

That being said, the proof of concept does indeed prove that this method works. And I do like the idea of not requiring a translations lookup table for every translatable object. Whether or not those positives outweigh the negatives depends on your own project requirements and your personal opinion as a developer (a cop-out answer, I know!).

Made with Jigsaw and Torchlight. Hosted on Netlify.