用学说2映射

Question

I have questions which are represented like this in my SQL database :

CREATE TABLE IF NOT EXISTS `t_question` (
  `id_question` int(10) NOT NULL AUTO_INCREMENT,
  `heading_key` varchar(255) NOT NULL,
  PRIMARY KEY (`id_question`)
) ENGINE=InnoDB;

Because I want my questions to be multilanguage, I link their keys to another table (t_lang_data) which stores strings for several uses (questions, articles and so on).

My t_lang_data has the folowing structure :

CREATE TABLE IF NOT EXISTS `t_lang_data` (
  `id_lang_data` int(10) NOT NULL AUTO_INCREMENT,
  `key` varchar(255) NOT NULL,
  `lang_iso` int(10) NOT NULL,
  `text` varchar(255) NOT NULL,
  PRIMARY KEY (`id_lang_data`)
) ENGINE=InnoDB;

So for example I could have this question :

id_question = 1 ; heading_key = heading_quest_1

And these lang_data entries :

id_lang_data = 1 ; key = heading_quest_1 ; lang_iso = en_UK ; text = "Heading for question 1" id_lang_data = 2 ; key = heading_quest_1 ; lang_iso = fr ; text = "Titre de la question 1"

I need to map these relations for Doctrine 2 but I do not know what to do. I have tried a many-to-one and a one-to-many approch but it does not work.

Do someone has an idea please ? Thanks

EDIT : I realize that it is even more difficult than what I thought. Indeed, my t_lang_data table can store strings for many tables such as t_questions, t_articles and so on. But in the mapping it seems that we can map only two tables together.

So how could I do ?

Answer 1

You indeed have one of the most common translation issue with databases.

If you want to have an efficient translation behavior without reinventing the wheel, you should use this library : https://github.com/l3pp4rd/DoctrineExtensions.

It has been recommended by the doctrine2 team and work perfectly well for me. The documentation dedicated to the translation behavior is here : https://github.com/l3pp4rd/DoctrineExtensions.

Also if you are using symfony2, the dedicated bundle is here : http:// github.com/stof/DoctrineExtensionsBundle

Moreover the translation behavior works just like your, by using a table dedicated to translated string storage referencing the original type and id.