Adding Translations to Model Using MySQL JSON Field in Laravel

Recently, I have been working on an application which needs to store a lot of metadata for the product model in JSON field format.

My first approach was to create sub-models to store information but that approach was not flexible. According to the requirements, we want to implement a solution where the product’s key-value pairs could be anything not fixed. Creating more sub-models will only provide me the fixed columns and won’t work at all.

Another solution which I tried was to store all metadata in a JSON file and then read it back, but storing data in files on the server was not an ideal solution from the security point of view.

As we know from MySQL 5.7.8 onward, JSON field types are supported natively, not only we can store data as a JSON format but also query the data using where class. So I decided to use the JSON field type to store all product-related metadata.

In this post, I will show you how we can store extra information in a JSON field and make a translation ready model with JSON type data. So let’s dive into.

For this post, one potential use-case I thought of was using it for localization purpose – storing different translations for a field in the database.

Let’s take a look at how storing translations for a model is typically done in a web application and how we can do the same job with MySQL’s native JSON type. Keeping it simple, say that the requirement is to store translations of title and body content into the database and retrieve it back. Not exactly a fully localized application but a good idea for testing something new.

I assume you have fresh Laravel project setup and database credentials are updated.

Common Approach

Normally we’d implement the above requirement by creating two models Post and PostTranslation. The first model will only contain language-neutral data: things like primary keys, fields that are the same across languages, etc. The second model contains the localized text, stored against the relevant ISO code of the language it represents.

The migration class of model Post will look like below:

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreatePostsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->increments('id');
            $table->string('slug')->unique();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('posts');
    }
}

And the model it self will be like below:

namespace App;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    protected $table = 'posts';

    protected $fillable = ['slug'];

    public function translations()
    {
        return $this->hasMany(PostTranslation::class);
    }
}

The migration class for model PostTranslation will look like below:

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreatePostTranslationsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('post_translations', function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedInteger('post_id');
            $table->string('locale');
            $table->string('title');
            $table->text('content');
            $table->timestamps();

            $table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('post_translations');
    }
}

And the model PostTranslation will look like below:

namespace App;

use Illuminate\Database\Eloquent\Model;

class PostTranslation extends Model
{
    protected $table = 'post_translations';

    protected $fillable = ['post_id', 'locale', 'title', 'content'];
}

Now run php artisan migrate and you will have two tables created posts and post_translations.

Add some relevant data into tables to test.

Now we want to load Post model with its translation based on the current locale of the application. We can use DB Query to join the two tables and load a specific post based on id and locale but I will use Eloquent because Eloquent is awesome.

We can load the post using below method:

Route::get('/posts', function () {

    $locale = Config::get('app.locale');

    $post = \App\Post::with(['translations' => function ($query) use ($locale) {
        $query->where('locale', '=', $locale);
    }])->whereId(1)->get();

});

Firstly we are getting the locale of our application and then loading the post with translations using eager loading and passing a closure to load the translations by locale. Now if you dump the results, you will find the post loaded with a post-translation based on locale.

Above approach is the best and tried since it follows the normalization principles. But we can simplify this with JSON field type without creating multiple tables.

JSON Field Approach

We can achieve the same thing using MySQL’s native JSON data type by chaging our model and migration for Post to below:

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreatePostsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->increments('id');
            $table->string('slug')->unique();
            $table->json('title');
            $table->json('content');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('posts');
    }
}

and post model to this:

namespace App;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    protected $table = 'posts';

    protected $fillable = ['slug', 'title', 'content'];
}

As you have seen, we are now storing title and content within the same table. You can discard the PostTransaltion model and migration all togather.

In above updated migration, we have set the title and content type to JSON. Now we will store title like below in the database.

{
    "en": "Hello world",
    "fr": "Bonjour le monde",
    "de": "Hallo welt"
}

Now to retrieve the post title and content, we will use the Laravel’s support for JSON format. Like below.

Route::get('/posts', function () {

    $post = \App\Post::where('title->fr', 'Bonjour le monde')->first();

    dd($post);
});

Now if you will go to /posts, you will find a record whose French title is “Bonjour le monde”. Pay special attention to where clause how we are getting the locale from the title’s JSON data.

Accessor for JSON

Until now, everything looks super simple with the JSON field type. You might be wondering how to get the values or read the values from a JSON based on a specific key. For example, we want to automate the whole process and get the values from the title and content field based on our current locale.

To achieve this, I will create a PHP Trait which can be used by any eloquent model to load the correct translation for a model field. I will create and new class in in app folder and name it Translatable with below code.

namespace App;

trait Translatable
{
    public function getAttribute($key)
    {
        if (isset($this->translatable) && in_array($key, $this->translatable)) {
            return $this->getTranslatedAttribute($key);
        }

        return parent::getAttribute($key);
    }

    protected function getTranslatedAttribute($key)
    {
        $values = $this->getAttributeValue($key);
        $primaryLocale = config('app.locale');
        $fallbackLocale = config('app.fallback_locale');

        if (!$values) {
            return null;
        }
        if (!isset($values[$primaryLocale])) {
            return $values[$fallbackLocale] ?: '';
        }

        return $values[$primaryLocale];
    }

    protected function isJsonCastable($key)
    {
        if (isset($this->translatable) && in_array($key, $this->translatable)) {
            return true;
        }

        return parent::isJsonCastable($key);
    }
}

In Translatable trait, we are overriding Eloquent’s getAttribute() with our own one. This method will be executed whenever we access the field of the model. We are checking that, if a field has a translation and if it has, we will return the correct one based on the current locale settings defined in the application config file. If there is no entry for a current locale then, we use the fall back if that doesn’t exist then, we will return an empty string.

All that’s left is hooking this trait up to a model.

namespace App;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    use Translatable;

    protected $table = 'posts';

    public $translatable = ['title'];

    public $casts = ['title' => 'json'];

    protected $fillable = ['slug', 'title', 'content'];
}

Now in our Post model, we are using the Translatable trait using use. We also have to tell the getAttribute() method what model fields are translatable.

We are also using the $cast property to let Laravel know that it should save this field as JSON when it persists to MySQL.

Final Words

Now you have seen, how we can use MySQL’s JSON type to create something useful.