На основе 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