Hey guys,
I'm really confused about making my new project's db multilanguage supported. I was using json for all fields but when i test json fields i see the performance is decreasing enormously when i add like 500k records. I have a e-commerce project and i am trying to use multilanguage in my db. I have tables like;
Products
Category
Collections
Attributes
and more tables like this. I want to make multilanguage supported for all my fields which users will interact. I'm really confused what should i do. Any ideas?
Kind regards.
How many languages do you want to support? Is it a matter of the site being bilingual, so you need, say, English and Spanish versions of all these fields? Or do you want product managers (or admins or whoever) to be able to add as many different languages as they want by selecting from a dropdown and filling out the title, description, etc., for each language they add? Or something else?
Just those two approaches would call for different solutions, and there are more possibilities.
@kokoshneta I want to create a system which admins can add their own "supported languages", so there is no limit. There can be only "English" or 10 languages. That's why i'm so confused.
@aosdev Yeah, that is a trickier one to deal with.
How many different column types do you have? In the examples you’ve given, your Attribute class just has a name, but the others all have a name/title (same thing, really) and description, as well as SEO-optimised title, description and keywords. If you can standardise name/title to just one of the two and you don’t have other tables that have completely different column names/types, then you’ve got a fairly good basis for doing it as a polymorphic relationship.
Essentially, you could then have a table called model_texts with a structure like this:
┌─────────────────┬────────────────────────────┐
│ column │ example value │
├─────────────────┼────────────────────────────┤
│ id │ 1 │
│ model_id │ 24 │
│ model_type │ App\Models\Product │
│ language_id │ 35 │
│ name │ A Name │
│ description │ Product Description │
│ seo_title │ SEO-friendly name │
│ seo_description │ Punchy Google description! │
│ seo_keywords │ product, thingy │
└─────────────────┴────────────────────────────┘
You’d have a unique constraint on that table on model_id, model_type, language so each entity/model instance can only have one set of model texts per language.
The ModelText model to match this table would look like this:
namespace App\Models;
class ModelText extends Model {
/**
* Get the parent model (product, category, etc.).
*/
public function model() {
return $this->morphTo();
}
}
To make it more flexible, I would make a trait to be used by those models that have translatable text properties:
namespace App\Traits;
use App\Models\ModelText;
trait HasTranslatableProps {
protected $with = ['language'];
/**
* Get the translated property’s language.
*/
public function language() {
return $this->belongsTo(Language::class);
}
/**
* Get all of the translatable properties.
*/
public function texts() {
return $this->morphMany(ModelText::class, 'model');
}
}
You could then easily retrieve all the translated texts for a given model instance:
// eager loading
$category = Category::with('texts')->find(287);
// lazy loading
$texts = Category::find(287)->texts;
// get texts in a specific language
$category->texts->firstWhere('language_id', Language::current());
(I’ve assumed here that you have a Language class that will take care of keeping track of all the possible languages admins can add, and that you’ve got all the necessary functionality to retrieve the current session language, compare language ID to a given language, etc.)
Please or to participate in this conversation.