phpmysqllaraveldatabasemultilingual

Storing 2 same id of post table with different language into database


I'm building a multi language website. In this case just 2 languages, it's Indonesian and English using Laravel. I have posts table, it will store id of each post and post_translations table is to store local, title, and description of post. I got the problem when storing data into database. I don't have any idea how to store post without inc the id except I've added 2 same post with Indonesian and English.

This is the result (wrong)

posts table

id 
1
2

post_translations table

id  post_id  locale   title
1     1      en       fisrt post
2     2      id       post yang pertama

Expexted result

posts table

id 
1

post_translations table

id  post_id  locale   title
1     1      en       fisrt post
2     1      id       post yang pertama

PostController

  public function store(Request $request) {
    $this->validate($request, [
        'title' => 'required',
        'slug' => 'required',
        'content' => 'required'
    ]);

    $post = new Post;

    $post->title = $request->title;
    $post->slug = $request->slug;
    $post->content = $request->content;

    $post->save();
    return redirect()->route('post.index');
}

Solution

  • Ok, so here we go (please note that this is not the only way):

    1. install spatie/laravel-translatable with

      composer require spatie/laravel-translatable

    **Note: for native spatie/laravel-translatable go to version 2 **

    1. create a table with this structure:

      CREATE TABLE `articles` (
        `id` int(10) UNSIGNED NOT NULL,
        `title` text COLLATE utf8_unicode_ci,
        `slug` text COLLATE utf8_unicode_ci,
        `content` text COLLATE utf8_unicode_ci,
        `created_at` timestamp NULL DEFAULT NULL,
        `updated_at` timestamp NULL DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
      

    Note: beter use a migration. I just exported a table I did earlier to test

    1. Insert the data in the datatabase in json format like this:

       INSERT INTO `pages` (`id`, `title`, `slug`, `content`, `created_at`, `updated_at`) VALUES
           (1, '{"ro":"Acas\\u0103","en":"Home"}', NULL, '{"ro":"<p><strong>Test title<\\/strong><\\/p>\\r\\n\\r\\n<p>Test content romanian<\\/p>\\r\\n","en":"<p><strong>test title english<\\/strong><\\/p>\\r\\n\\r\\n<p>test content english.<\\/p>\\r\\n"}', '2017-04-03 11:45:56', '2017-04-03 12:15:16');
      
    2. Now create the blade to edit, update, create show etc. To get the language do something like this in the blade:

      {{ $data->title }}
      
      {!! nl2br($data->content) !!}
      
    3. And in the controller: add something like this:

      /**
            * Generate the field by language
            *
            * @param   Model        $entry      the item selected from the database
            *
             * @return  array
            */
      
       public function getTranslatableFields($fields)
       {
           $inputs = [];
           $languages = $this->getLanguages();
      
           foreach ($languages as $language) {
               foreach ($fields as $field) {
                   $inputs[] = [
                       'name' => "{$field['name']}[{$language->abbr}]",
                       'label' => $field['label'] . " ($language->abbr)",
                       'lang' => $language->abbr,
                       'type' => array_key_exists('type', $field) ?     $field['type'] : 'text'
                   ];
               }
           }
      
           return $inputs;
       }
      

    I added this function in a LangTrait. Since I also use backpack for laravel I did some more things.

    For edit I added this method in the trait:

    /**
         * Show the form for editing the specified resource.
         *
         * @param   int             $id             the item's identifier
         *
         * @return  Response
         */
        public function edit($id)
        {    
            $data['entry'] = Model::find($id);
            $data['title'] = trans('lang_file.edit').' '.$this->entity_name; // name of the page 
            $data['fields'] = $this->getMultiLangFields($data['entry']);
            $data['id'] = $id;
    
            return view('crud::edit', $data);
        }
    
    
    /**
         * Generate the field by language
         *
         * @param   Model        $entry      the item selected from the database
         *
         * @return  array
         */
        protected function getMultiLangFields($entry)
        {
            $fields['id'] = ['name' => 'id', 'type' => 'hidden', 'value' => $entry->id];
    
            foreach ($this->crud->update_fields as $key => $field) {
                $value = null;
    
                if (array_key_exists('lang', $field)) {
                    $name = preg_replace('/(\[\w{2}\])$/i', '', $field['name']);
                    $value = $entry->getTranslation($name, $field['lang']);
                }
    
                $fields[$key] = array_merge($field, ['value' => $value]);
            }
    
            return $fields;
        }
    
     
    
    /**
         * Get the application active languages
         *
         * @return  \Backpack\LangFileManager\app\Models\Language
         */
        protected function getLanguages()
        {
            return Language::whereActive(1)->orderBy('default', 'desc')->get();
        }
    
    1. In my main controller I did: use LangTrait; (contains everything above)

      In construct I added this:

      $this->getTranslatableFields($fields)
      

      where $fields it's the list of fields I need

      All methods should be adapted to you html format. As I said I use backpack for Laravel and fields are formatted accordingly.

    2. And finally for the getLanguage file to work I created a new table and a model in the DB:

      Model:

      class Language extends Model
      {
          protected $table = 'languages';
      
          protected $fillable = ['name', 'flag', 'abbr', 'native', 'active', 'default'];
      
          public $timestamps = false;
      
          public static function getActiveLanguagesArray()
          {
              $active_languages = self::where('active', 1)->get()->toArray();
              $localizable_languages_array = [];
      
              if (count($active_languages)) {
                  foreach ($active_languages as $key => $lang) {
                      $localizable_languages_array[$lang['abbr']] = $lang;
                  }
      
                  return $localizable_languages_array;
              }
      
              return config('laravellocalization.supportedLocales');
          }
      
          public static function findByAbbr($abbr = false)
          {
              return self::where('abbr', $abbr)->first();
          }
      }
      

      Table:

      CREATE TABLE `languages` (
        `id` int(10) UNSIGNED NOT NULL,
        `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
        `app_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
        `flag` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
        `abbr` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
        `script` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
        `native` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
        `active` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
        `default` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
        `created_at` timestamp NULL DEFAULT NULL,
        `updated_at` timestamp NULL DEFAULT NULL,
        `deleted_at` timestamp NULL DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
      

      Data in the table:

      INSERT INTO `languages` (`id`, `name`, `app_name`, `flag`, `abbr`, `script`, `native`, `active`, `default`, `created_at`, `updated_at`, `deleted_at`) VALUES
      (1, 'English', 'english', '', 'en', 'Latn', 'English', 1, 0, NULL, NULL, NULL),
      (2, 'Romanian', 'romanian', '', 'ro', 'Latn', 'română', 1, 1, NULL, NULL, NULL);
      

    Since I did this through a package I kind of messed around a little bit with the code.


    Now, for the spatie/laravel-translatable package version:

    1. set up the service provider in config/app.php add this in the providers array:

      Spatie\Translatable\TranslatableServiceProvider::class,
      

      In the model Articles add use HasTranslations; like this:

      use Illuminate\Database\Eloquent\Model;
      use Spatie\Translatable\HasTranslations;
      
      class NewsItem extends Model
      {
          use HasTranslations;
      
          public $translatable = ['name']; // list the columns you want to be translatable (will have json format)
      }
      
    2. save a new entry and use it:

      $article = new Article;
      $article->setTranslation('name', 'en', 'Updated name in English')
              ->setTranslation('name', 'nl', 'Naam in het Nederlands');
      
      $article->save();
      
      $article->name; // Returns 'Name in English' given that the current app locale is 'en'
      $article->getTranslation('name', 'nl'); // returns 'Naam in het Nederlands'
      
      app()->setLocale('nl');
      
      $article->name; // Returns 'Naam in het Nederlands'
      
    3. examples from: https://github.com/spatie/laravel-translatable

    4. Database table format is as stated above in the first version

    If it does not work out let me know and I'll look at your code.