Planet CakePHP

Use case: Show translation of database content.

To start with translation of database content in CakePHP 1.2 we have to create a table to store all translated data, by default it’s name is i18n. We can use ‘cake i18n’ command to create the table. Here is the SQL of the table I’ve got:

CREATE TABLE `i18n` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `locale` varchar(6) collate utf8_unicode_ci NOT NULL,
  `model` varchar(255) collate utf8_unicode_ci NOT NULL,
  `foreign_key` int(11) NOT NULL,
  `field` varchar(255) collate utf8_unicode_ci NOT NULL,
  `content` text collate utf8_unicode_ci,
  PRIMARY KEY  (`id`),
  KEY `locale` (`locale`),
  KEY `model` (`model`),
  KEY `foreign_key` (`foreign_key`),
  KEY `field` (`field`)
)

I will localize Country names, so here is sql for counries table:

CREATE TABLE `countries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`)
)

Just a few words about the table: the idea of translation is to intercept calls to the model data and to lookup for translated content in the i18n table. Translation content identified by model name, current locale, model identifier (id) and field name to be translated.
So setup fields to be translated we have to add behavior in to the model and supply model field names:

var $actsAs   = array('Translate' => array('name'));

My model name is ‘Country’. After adding behavior to the model in the sql trace I can see additional query to the database:

SELECT `Country`.`id`, `I18n__name`.`content` FROM `countries` AS `Country` LEFT JOIN `i18n` AS `I18n__name` ON (`Country`.`id` = `I18n__name`.`foreign_key` AND `I18n__name`.`model` = 'Country' AND `I18n__name`.`field` = 'name') WHERE `I18n__name`.`locale` = 'eng'

It’s TranslateBehavior is looking for translated content. I have about 200 records in countries table but after adding the behavior the results are empty. It looks like only translated content records appear in the results.

Found a problems

1) Find as list does not load translated content. (problem 4456). Following code will return identifiers but translated values always will be set to null.

$countries = $this->Somemodel->Country->find('list'); //returns array with null values

2) If there are multiple model associations has-one to the same model then i18n content searches by model.name, not by model.alias (problem 4461).

That’s all. Happy baking.

P.S.:

Why I need Country.name column in the database table if contents for the name column is stored in the i18n table? I’ve renamed the column to ‘name_old’ and it still works, buy in lists only identifier are displayed. After following line was added translated values appeared again in the lists.

var $displayField = 'name';

So there is no column in the databse but in model it’s created from translation behavior. Now countries table has following structure:

CREATE TABLE `countries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`id`)
)

P.S.:

To sort by localized values use ‘I18n__.content’. For example to sort countries list by localized name I use:

var $order = 'I18n__name.content';

Open questions

1. Why contents not localized for related models one-to-many, many-to-one, many-to-many (HABT)?
2. Why order column should be ‘I18n__name.contents’?

What next?

Take a look at the alternative solution in the next post.