本地化数据库结果

Question

I have developed a simple application form that I serve in two languages. I'm also populating dropdown menus from the database and I'm struggling with how to present that data in two languages. The data is not very dynamic and yet time to time new entries might be added so manual work won't help me. I'm also pulling different number of rows for the different cases. I'm confused, I think unless I have two databases or add the translation to the database and present it with the original text, I have no other options. Or a new column in the database that will hold the tranlation and then pull the correct column according to the locale chosen... Any ideas would be appreciated, thanks!

Answer 1

The way I see it, the best option in your case would be creating a separate table for storing translations of each record.

For example, entry like a blog post would have some fields that are common in all site languages, but things like a title, content and slug would be locale specific. When user requests the information, all the common information would be retrieved from 'posts' table, while the locale specific translations would be retrieved from the 'posts_translations' table.

This way you wouldn't have to manually add column for every translation. While technically that would work just fine, you would run into problems, if you ever had to add another language(s). If translations are stored in separate tables, that is no longer a problem.

Here's an example how that might look when using Eloquent ORM:

public function getPosts()
{
    $language = App::getLocale();
    $posts = Post::with([
        'translation' => function ($query) use ($language) {
                $query->where('language', '=', $language);
        }
    ])->get();
    return $posts;
}

Alternatively, the same method using Laravel's Query Builder:

public function getPosts()
{   
    $language = App::getLocale();
    $posts = DB::table('posts')
    ->leftJoin('posts_translations', 'posts.id', '=', 'posts_translations.post_id')
    ->where('posts_translations.language', '=', $language)
    ->get();
    return $posts;
}