Translatable Columns Using Laravel

Brief
In multi-language projects, it’s essential to store phrases in different languages for text columns. For example, a multi-language articles site needs to store the title and body phrases for all supported languages. This can become a challenge if not handled correctly. In this article, we will explore different architectures with examples, comparing query efficiency and modifiability between them.

Architectures

1- Separate Columns for Each Language

The simplest architecture involves having a column for each language, such as title_ar and title_en. Using Laravel, working with this setup can be straightforward as shown in the example below.

Pros:
Phrases are treated as regular text columns, allowing for efficient search and sort operations using SQL.
Easy to implement.

Cons:
Limited modifiability; adding a new language requires running a migration to add new columns.
Having multiple translatable columns for multiple languages can rapidly increase the column count, potentially affecting query efficiency.

Example:

// Migration for articles table
Schema::create(‘articles’, function (Blueprint $table) {
    $table->id();
    $table->string(‘title_en’);
    $table->string(‘title_ar’);
    $table->text(‘body_en’);
    $table->text(‘body_ar’);
    $table->timestamps();
});

//Article model class
use Illuminate\\Database\\Eloquent\\Model;
use Illuminate\\Support\\Facades\\App;

class Article extends Model
{
    // List of fillable attributes
    protected $fillable = [‘title_en’, ‘title_ar’, ‘body_en’, ‘body_ar’];

   protected function title(): Attribute
    {
        return Attribute::make(
            get: fn() => $this->getTranslatableAttribute(‘title’),
            set: fn($value) => $this->setTranslatableAttribute(‘title’, $value)
        );
    }

    protected function body(): Attribute
    {
        return Attribute::make(
            get: fn() => $this->getTranslatableAttribute(‘body’),
            set: fn($value) => $this->setTranslatableAttribute(‘body’, $value)
        );
    }

    private function getTranslatableAttribute($attribute)
    {
        $locale = App::getLocale();
        $localizedAttribute = "{$attribute}_{$locale}";
        return $this->{$localizedAttribute};
    }

    private function setTranslatableAttribute($attribute, $value)
    {
        $locale = App::getLocale();
        $localizedAttribute = "{$attribute}_{$locale}";
        $this->attributes[$localizedAttribute] = $value;
    }
}

2- JSON Column (Spatie Package)
Using JSON columns can avoid creating many separate columns but adds complexity to sorting, searching, and querying. This architecture is suitable for projects that do not require complex queries on the phrases. You can easily apply this architecture in Laravel by installing the laravel-translatable package from Spatie.

Pros:
Easy to implement using the Spatie package.
Avoids a large number of columns.

Cons:
Increased complexity in handling JSON.
Sorting, searching, and querying can become complex and inefficient.

Example:

// Migration for articles table with JSON column
Schema::create(‘articles’, function (Blueprint $table) {
    $table->id();
    $table->json(‘title’);
    $table->json(‘body’);
    $table->timestamps();
});

// Sample usage with Spatie package
use Spatie\\Translatable\\HasTranslations;

class Article extends Model
{
    use HasTranslations;

    public $translatable = ['title', 'body'];
}

3- Separate Phrases Table
Using a separate table to store phrases is the most suitable option for projects that heavily rely on multiple languages. Regardless of the number of languages or rows, it involves a simple join operation. However, implementing this architecture can be complex.

Pros:
Efficient queries.
Phrases are treated as regular text columns, allowing for efficient search and sort operations using SQL.
Adding or deleting a language does not require significant changes.

Cons:
Complexity in implementation.
Requires joining with another table to retrieve the phrases.

Example:

// Migration for articles table
Schema::create('articles', function (Blueprint $table) {
    $table->id();
    $table->timestamps();
});

// Migration for phrases table
Schema::create('phrases', function (Blueprint $table) {
    $table->id();
    $table->foreignId('article_id')->constrained()->onDelete('cascade');
    $table->string('language');
    $table->string('key');
    $table->text('value')->nullable();
    $table->timestamps();
});

// Article model class
class Article extends Model
{
    public function phrases()
    {
        return $this->hasMany(Phrase::class);
    }

    protected function title(): Attribute
    {
        $locale = App::getLocale();
        return Attribute::make(
            get: fn() =>  $this->phrases
            ->where('key', 'title')
            ->where('language', $locale)
            ->first()
            ?->value ?? '',
        );
    }

    protected function body(): Attribute
    {
        $locale = App::getLocale();
        return Attribute::make(
            get: fn() =>  $this->phrases
            ->where('key', 'body')
            ->where('language', $locale)
            ->first()
            ?->value ?? '',
        );
    }
}

//Phrases model class
class Phrase extends Model
{
    public function article()
    {
        return $this->belongsTo(Article::class);
    }
}

In conclusion, the choice of architecture depends on the specific needs of your project. For simple projects with a limited number of languages, separate columns might suffice. For projects requiring flexibility and scalability, using a separate phrases table is more efficient, despite the complexity of implementation. The JSON column approach can be a middle ground but requires careful handling of query operations.

If you decide to use a separate phrases table for the sake of flexibility and scalability, consider trying the Translatable-pro package from Larazeus. This package is designed for performance, storing phrases in separate database tables to simplify the maintenance of translations across all languages. With just one Composer install, you can seamlessly integrate comprehensive multi-language support into your app, enabling you to create advanced, optimized, and high-performance translatable applications with an efficient database structure which supports filament also.
For more information, visit
https://larazeus.com/translatable-pro