Can you provide an example of translating a JSON column using Spatie

1. Installation and Setup

- First, require the package via Composer:

bash
composer require spatie/laravel-translatable

- In your Eloquent model, add the `HasTranslations` trait and define a public `$translatable` property listing the attributes you want to translate. For example, if you have a `Post` model with a `title` column that stores translations:

php
use Illuminate\Database\Eloquent\Model;
use Spatie\Translatable\HasTranslations;

class Post extends Model
{
    use HasTranslations;

    public $translatable = ['title'];
}

- Make sure the `title` column in your database is of type JSON (or TEXT if JSON is not supported). This column will store translations in JSON format, e.g., `{"en": "Hello", "fr": "Bonjour"}`[5][6][9].

2. Setting Translations

You can set translations in multiple ways:

- Set translation for the current locale simply by assigning a string:

php
$post = new Post();
$post->title = 'Hello'; // sets 'en' translation if app locale is 'en'
$post->save();

- Set multiple translations at once by assigning an array:

php
$post->title = ['en' => 'Hello', 'fr' => 'Bonjour'];
$post->save();

- Or use the `setTranslation` method to set a translation for a specific locale:

php
$post->setTranslation('title', 'en', 'Hello');
$post->setTranslation('title', 'fr', 'Bonjour');
$post->save();

3. Getting Translations

- Accessing the attribute returns the translation for the current app locale:

php
app()->setLocale('fr');
echo $post->title; // Outputs: Bonjour

- To get a translation for a specific locale:

php
echo $post->getTranslation('title', 'en'); // Outputs: Hello

- To get all translations as an array:

php
$translations = $post->getTranslations('title'); // ['en' => 'Hello', 'fr' => 'Bonjour']

4. Querying Translations

Since translations are stored as JSON, you can query specific translations using JSON operators supported by your database:

- For MySQL 5.7+ or MariaDB 10.2.3+, you can query like this:

php
$posts = Post::where('title->en', 'Hello')->get();

or using raw JSON extract:

php
$posts = Post::whereRaw("JSON_EXTRACT(title, '$.en') = 'Hello'")->get();

- You can also use helper methods provided by the package to query by locale:

php
$posts = Post::whereLocale('title', 'en')->get();

or query for multiple locales:

php
$posts = Post::whereLocales('title', ['en', 'fr'])->get();

5. Translating Nested JSON Keys

If your JSON column contains nested keys, you can translate those by specifying the path in the `$translatable` array and using the `->` notation:

php
public $translatable = ['meta->description'];

// Setting translations
$post->setTranslation('meta->description', 'en', 'English description');
$post->setTranslation('meta->description', 'fr', 'Description en français');
$post->save();

// Getting translations
echo $post->getTranslation('meta->description', 'fr'); // Description en français

This approach leverages Spatie's package to store all translations in a single JSON column, simplifying database structure while providing easy access and querying of translations. It is important that your database supports JSON columns for best performance and querying capabilities. If not, the package falls back to TEXT columns but with limited querying features[5][6][9].

Citations:
[1] https://spatie.be/docs/laravel-tags/v4/advanced-usage/adding-translations
[2] https://github.com/spatie/laravel-ciphersweet/discussions/8
[3] https://dev.to/biostate/how-to-order-spatie-translatable-columns-in-laravel-37f0
[4] https://spatie.be/docs/laravel-translatable/v6/basic-usage/querying-translations
[5] https://laraveldaily.com/lesson/multi-language-laravel/spatie-laravel-translatable
[6] https://blog.yanalshoubaki.com/how-to-make-translatable-laravel-app-72e983571ee9
[7] https://laraveldaily.com/post/laravel-why-not-use-json-db-columns-practical-examples
[8] https://dev.to/yanalshoubaki/how-to-make-translatable-laravel-app-19dg
[9] https://github.com/spatie/laravel-translatable
[10] https://www.answeroverflow.com/m/1148602552871551137