Multilingual database model approaches

schmunk · November 30, 2011, 6:55pm

Starting from the discussion at stackoverflow.com I thought it would be more productive to discuss this topic here.

Avalon

I’m having a problem getting the data from my database which I created to be completely multilingual, and I hope someone here can help me.

I’ve split up all my tables in 2 parts; the “universal” table (does not contain any text that needs to be translated) and the table which contains all the fields that need to be translated with their translations.

Example tables:
base_material


    id


    picture


base_material_i18n


    base_material_id


    localization_id


    name


    description


    review_status


    review_notes


localization


    id


    language_name
Query to get the translations (using English (en) as a fall-back language if there is no translation available):
SELECT o.id


     , o.type


     , o.code


     , o.position


     , ifnull(t.name,d.name) name


     , ifnull(t.description,d.description) description


  FROM base_material o


       INNER JOIN base_material_i18n d


               ON ( o.id=d.base_material_id)


       LEFT OUTER JOIN base_material_i18n t


                    ON ( d.base_material_id=t.base_material_id AND t.localization_id='nl' )


 WHERE d.localization_id='en'
My question is how I can automatically get those translations (with the fall-back language as in this query) attached to my model in Yii when I’m searching for the base_material objects? (This is only 1 example table, but almost all my tables (20+) are built in this way, so if possible I would be needing something flexible)

An example of an existing system using what I would need is Propel: http://propel.posterous.com/propel-gets-i18n-behavior-and-why-it-matters

Any ideas how to go about doing that? I’ve checked the existing Yii extensions regarding multilingual sites (like Multilingual Active Record), but they all use a different database design (general information+fall-back language in the main table, translations in the i18n table), and I’m not sure how to change those extensions to use my kind of DB model.

If someone knows of a way to change that existing extension so it can use my kind of DB scheme, then that would be absolutely brilliant and probably the best way to do this.

Edit: I’ve added a bounty because I still can’t find anything on how to let Propel work with Yii (there does exist an extension for Doctrine, but Doctrine doesn’t support this kind of DB model with translations either), nor any more information as to how to deal with this using an existing Yii extension or with scopes.

Edit: 98 times viewed but only 3 upvotes and 1 comment. I can’t help feeling like I’m doing something wrong here, be it in my question or application/database design; either that or my problem is just very unique (which would surprise me, as I don’t think my multilingual database design is that absurd ;-). So, if anyone knows of a better all-round solution for multilingual sites with Yii and/or Propel (apart from the current extensions which I really don’t like due to the duplication of text fields) or something similar, please let me know as well.

Thanks in advance!

schmunk

I am also looking for a generic solution to implement i18n into Yii models. Recently I chose a very similar database schema for a project like you. The only difference is, that I am not using a separate language table, I store the language information in the i18n table.

The following solution is without a custom SQL statement, but I think this could be implemented with relation params, anyway, if you’re working with foreign key in your database (eg. MySQL InnoDB) gii will create relations between your base_material and base_material_i18n table, like

class BaseMaterial extends CActiveRecord

public function relations()

{
return array(


    'baseMaterialI18ns' =&gt; array(self::HAS_MANY, 'base_material_i18n', 'id'),


);
}

class BaseMaterialI18n extends CActiveRecord

public function relations()

{
return array(


    'baseMaterial' =&gt; array(self::BELONGS_TO, 'base_material', 'id'),


);
}

Now you’d be able to access your translations by using the object notation for relations.

$model = BaseMaterial::model()->with(‘baseMaterialI18ns’)->findByPk(1);

foreach($model->baseMaterialI18ns AS $translation) {

if ($translation->language != "the language I need") continue:

// do something with translation data …

}

I thought about creating a behavior or base class for those models which would act a as helper for managing the translations - pseudo code:

I18nActiveRecord extends CActiveRecord

protected $_attributesI18n;

// populate _attributesI18n on query …

public function __get($name) {

if(isset($this->_attributesI18n[‘language_I_need’][$name]))
return &#036;this-&gt;_attributesI18n[&#036;name];
else if(isset($this->_attributesI18n[‘fallback_language’][$name]))
return &#036;this-&gt;_attributesI18n[&#036;name];
else
parent::__get();
}

CActiveRecord __get() source

There is more work to be done to find the needed i18n record, also you could further limit the with() option to improve performance and reduce parsing on the PHP side.

But there may be different use cases how to determine the value, e.g. all translations, translation or fallback, no fallback (empty value). Scenarios could be helpful here.

PS: I would be up for a github project!

Thanks for your answer schmunk! Indeed the second part of your answer is more or less what I’d be looking for. The first part I perfectly understand and would work, but is too troublesome with all tables and all fields. Clearly you have more experience with Yii than I do; would it be possible to elaborate a bit on that base class? I get the idea of what you try to do, but haven’t got a clue on how to actually do that. – Avalon Nov 15 at 12:23

1

Just created a behavior for that. I found that it’s cleaner if you access translations by a t() method. Let’s have a look github.com/schmunk42/p3extensions/blob/master/behaviors/… – schmunk Nov 22 at 1:02

This looks brilliant, thanks for keeping me (and this question) updated! I think I get the gist of how it works and how to use it and I should be able to try it out tomorrow evening. I assume the ‘relation’ => ‘daContentTranslations’ would become ‘relation’ => ‘baseMaterialI18ns’ using the first part of the example code in your answer? And if I use the same relation name on all my tables, this would work on all of them? Again; thank you so much! – Avalon Nov 22 at 10:47

Also, could you explain a little why exactly this is "cleaner"? It seems to me that the other option is even more universal and uses less code? – Avalon Nov 22 at 12:39

Yes ‘baseMaterialI18ns’ should be your relation. You can use different relation names for your translation tables, just specify the appropriate params as shown in the comment section. There is still some work to be done, i.e. customizing the ‘not yet translated**’ string. First, I tried to populate some internal arrays of CActiveRecord, but I ran into problems, I am also unsure if you can override __get() with a behavior?! Base classes are not that flexible, if you already have a base class for your models, which is likely on a large scale project. you’d have to do some extra coding. – schmunk Nov 23 at 17:55

Please let me know, if you’ve improvements for this component. – schmunk Nov 23 at 17:55

I’ve managed to get it working though I had to change “$models[$translationModel->language] = $translationModel;” to “$models[$translationModel->localization_id] = $translationModel;”. Maybe this should be turned into a setting (I also added “$fallback” to the general settings and set it default to true as that seems more logical to me (better to show a message in the “wrong” language than no message at all). A limitation of this technique is that you cannot return a completely translated model but only 1 attribute at the time, I assume? Is there a way to do this? Thanks again btw! – Avalon Nov 25 at 17:40

Just a clarification about that last question; I wonder if it would be possible to have something like BaseMaterial::model()->findAll()->t()->name; instead of BaseMaterial::model()->findByPk(1)->t(‘name’); where the t() function would return all the models with their translations? – Avalon Nov 25 at 18:14

Just wanted to let you know, a few days ago the Multilingual extension has had an update: yiiframework.com/extension/multilingual-active-record with a link to the forum where he is now using a behaviour as well. Might be worth checking it out! – Avalon Nov 27 at 15:15

The component you mentioned does a lot of "magic" as far as I can tell, e.g. it also handles saving. And it does not use your schema. – schmunk yesterday

True, but apparently it is possible to use it with my schema as well by adding the translatable attributes as properties on the main model and then using the afterFind() method to set the fallback data if there is no translation available. The advantage here is being able to translate an entire Model and the related Models at once – Avalon 6 hours ago

I thought your model would not use translatable attributes in main table? My approach can also set a fallback language. What do you mean with translate an entire model, do you also want to translate ‘picture’? And do you mean with ‘at once’, that the base class handles the saving? – schmunk 14 mins ago