Garden Framework

На основе vanillaforums/Garden #551: Multi translate for any content. Задача сделать структуру для локализации контента, причем количество языков — неизвестно, как и количество полей.

1. Структура

Задачу будем решать с помощью модели EAV (Entity-attribute-value Model).

Gdn::Structure()
    ->Table('Localization')
    ->Column('Language', 'char(2)')
    ->Column('Name', 'varchar(50)', False, 'index') // Table name
    ->Column('Field', 'varchar(50)')
    ->Column('RowID', 'int', False)
    ->Column('Value', 'text', True)
    ->Set();

Структура довольно понятна и упрощена (денормализована). О недостатках поговорим позже, сейчас хотелось бы отметить преимущество — изменения в структуру больше не потребуется.
Language — двух-буквенный код языка
Name — имя таблицы с полем Field, содержимое которого нужно перевести
Field — имя поля из таблицы Name
RowID — значение первичного ключа таблицы Name
Value — перевод содержимого

По-хорошему, индексы должны быть на полях Language и Name. Скорее всего, в большинстве случаев, будет использоваться 2-3 языка, поэтому смысла добавлять в индекс Language нет — селективность высокая. Поэтому ограничимся индексом поля Name.

2. Преобразование строк в колонки

По иностранному это называется pivot. Существует несколько способов развернуть таблицу. Рецепт развертывания, шаблон запроса в сыром виде:

select 
    group_concat(if(Field = 'FIELD1', Value, NULL)) as `FIELD1`,
    group_concat(if(Field = 'FIELD2', Value, NULL)) as `FIELD2`,
    ...
    group_concat(if(Field = 'FIELDN', Value, NULL)) as `FIELDN`,
    RowID
from Localization
where Language = 'LANGUAGE_CODE' and Name = 'TABLE_NAME'
group by RowID

Если используемая вами СУБД не поддерживает group_concat() можно использовать функцию max(). В результате получаем значение первичного ключа RowID, набор переводов полей FIELD1, FIELD2, …, FIELDN языка LANGUAGE_CODE для таблицы TABLE_NAME. Думайте об этом результате как о таблице, которую нужно соеденить с основной.

3. Пример

К примеру, у нас есть приложение типа «Каталог» с двумя локализированным полями «Название» (Name) и «Описание» (Description).

Gdn::Structure()
    ->Engine('MyISAM')
    ->Table('CatalogItem')
    ->PrimaryKey('CatalogItemID', 'usmallint')
    ->Column('Name', 'varchar(200)')
    ->Column('Description', 'text', True)
    ->Column('Price', 'float')
    ->Column('Image', 'varchar(250)', True)
    ->Set();

a) Преобразование таблицы Localization (это пример кода внутри какой-то модели: $this->Name — имя таблицы):

$LocaleName = Gdn::Config('Garden.Locale');
$LanguageCode = ArrayValue(0, explode('-', LocaleName));
Gdn::SQL()
    ->Select("if(Field = 'Name', Value, Null)", 'group_concat', 'Name')
    ->Select("if(Field = 'Description', Value, Null)", 'group_concat', 'Description');
$InnerSQL = Gdn::SQL()
    ->Select('RowID')
    ->From('Localization')
    ->Where('Language', $LanguageCode)
    ->Where('Name', $this->Name)
    ->GroupBy('RowID')
    ->GetSelect();
$InnerSQL = $this->SQL->ApplyParameters($InnerSQL);
Gdn::SQL()->Reset();

1. Первые строчки — определение какой язык используется (здесь язык берется из конфига, но ничто не мешает передать его как параметр).
2. Далее, выбор полей, тоже заданы жестко, и также ничто не мешает передать их параметром (*).
3. Цепочка построения подзапроса, InnerSQL — параметризированный текст sql запроса (с токенами).
4. ApplyParameters() подставляет в параметры значения.
5. Reset() сбрасывает все параметры.

b) Соединение с основной таблицей ‘CatalogItem’:

$SQL = Gdn::SQL();
$SQL
    ->Select('ci.Name, ci.Description, ci.Price, ci.Image')
    ->From('CatalogItem ci');
// Трюк с пустым префиксом.
$Prefix = $SQL->Database->DatabasePrefix;
$SQL->Database->DatabasePrefix = '';
$SQL->Join("($LozalizedTable) lt", 'lt.RowID = ci.CatalogItemID', 'left');
$SQL->Database->DatabasePrefix = $Prefix;
$SQL
    ->Select('lt.Name as LocalizedName')
    ->Select('lt.Description as LocalizedDescription');

В дополнительных полях LocalizedName и LocalizedDescription мы получаем наш перевод, NULL если перевода нет. Обратите внимание! Что перед JOIN-ом нужно применить трюк с пустым префиксом таблиц, иначе метод Gdn_SQLDriver::MapAliases() добавит префикс и подзапрос получится с синтаксической ошибкой вида:

... Prefix_(select ... )

4. Финал

Если все оформить в методы модели, то должно получится примерно такое:

protected function JoinWithSql($Sql, $On, $Type = 'left') {
    // Трюк с пустым префиксом, инача SqlBuilder выдаст ошибку.
    $Prefix = $this->SQL->Database->DatabasePrefix;
    $this->SQL->Database->DatabasePrefix = '';
    $this->SQL->Join($Sql, $On, $Type);
    $this->SQL->Database->DatabasePrefix = $Prefix;
}

protected function GetLocalizedSqlTable($Fields) {
    $LanguageCode = LocaleLanguageCode();
    if (is_string($Fields)) $Fields = explode(',', $Fields); // (*)
    foreach ($Fields as $Field) 
        $this->SQL->Select("if(Field = '$Field', Value, Null)", 
            'group_concat', $Field);
    $InnerSQL = $this->SQL
        ->Select('RowID')
        ->From('Localization')
        ->Where('Language', $LanguageCode)
        ->Where('Name', $this->Name)
        ->GroupBy('RowID')
        ->GetSelect();
    $InnerSQL = $this->SQL->ApplyParameters($InnerSQL);
    $this->SQL->Reset();
    return $InnerSQL;
}

И потом где-нибудь в public методах можно использовать:

$LozalizedTable = $this->GetLocalizedSqlTable('Name, Description'); // (*)
...
$this->JoinWithSql("($LozalizedTable) lt", 'lt.RowID = ci.CatalogItemID', 'left');
$SQL
    ->Select('lt.Name as LocalizedName')
    ->Select('lt.Description as LocalizedDescription');

Методы выше получает дополнительные поля наряду с основными, если это не нужно, то можно сделать так:

$SQL->Select('lt.Name, ci.Name', 'ifnull', 'Name');
$SQL->Select('lt.Description, ci.Description', 'ifnull', 'Description');

Если нет перевода, то значение поля берется из основной таблицы.
Заметьте, что $this->GetLocalizedSqlTable() начинает новую цепочку построения запроса, так что убедитесь что до этого вызова $SQL «чистый», иначе все параметры попадут в $InnerSQL.

5. Прозводительность

Прозводительность конечно зависит от основного запроса. Explain говорит что DERIVED Localization таблица будет просматриваться вся (type = ALL), но я ему не верю. Если сделать explain отдельно по дополнительному запросу, то:

select_type: SIMPLE
        table: Localization
         type: ref
possible_keys: Name
          key: Name
      key_len: 152
          ref: const
        Extra: Using where; Using filesort

На тестовой табличке с ~ 2 миллионами строк запрос выполняется менее чем 0.01 секунды, так что с производительностью тут все нормально.

Update: Индекс используется. При игнорировании индекса, запрос занял 0.5 секунды.

Ссылки

EAV Model:
http://ycmi.med.yale.edu/nadkarni/eav_CR_contents.htm
http://en.wikipedia.org/wiki/Entity-attribute-value_model
Pivot tables (crosstabs):
http://www.artfulsoftware.com/infotree/queries.php#78
http://en.wikibooks.org/wiki/Programming:MySQL/Pivot_table
MySQL ifnull function:
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull