Spiga

Multilanguage models in zend framework using Zend_Db_Table

by Gabi Solomon

As i wrote in a previous post there are more ways to design a database for a multilanguage website, but the one i decided to go with is Coupled Translation table approach, where basicly for each table you have another one where you store a row for each language wich has the primary table id, language id and the other columns that need to be translated.

The Requirements and Implementation

Having this idea in mind i wrote a new class that extends Zend_Db_Table to provide an easy access to the translated tables. Basically the class a couple of things:

1. Getting the right translated info
It overwrites the default select method so you can basicly use Zend_Db_Table as normal to fetch rows, but you will actualy get a join of 2 tables as a result (primary table and translate table joined on the default language).

2. Getting form data
Of course you will need a method to add this data into your database … and you cant do it by hand.
There is a method called formInfo wich takes a parameter an ID that will return a model with language columns.
To be more explicit i will give an example … if you have as translated columns the columns name and description and as languages en and es you will get this new columns:
name_en
name_es
description_en
description_es

3. Saving form data
After populating the form with formInfo you will need to save your model. So i overwrote the methods insert and update to allow separation of columns from the main table and the language table (the ones i explained above)

4. Deleting Data
I also overwrote the delete method to allow the deletion of the language table rows as well.

The actual Class

Below you have the actual class file … you will have to excuse me because it isnt commented.
The class is still in testing in a project i am making at the moment, and hopefully when its fully tested i will comment it.
Or maybe someone will show me i reinvented the wheel as on a previous post. :) )
[php]

abstract class GSD_Db_TableLang extends Zend_Db_Table
{

public function formInfo($id){
$select = new Zend_Db_Table_Select($this);
$row = $this->fetchRow($select->where(‘id = ?’, $id));
$rowArray = $row->toArray();

if ($row) {
$rowArray = $this->getLangArray($rowArray);
}

return $this->buildLangRow($rowArray);
}

function getLangArray($rowArray){
$langRows = $this->_db->fetchAll(
$this->_db->select()
->from($this->getLangTable(), $this->lang_fields)
->join(‘languages’,
$this->getLangTable() . ‘.language_id = languages.id’, ‘code’)
->where($this->_sName . ‘_id = ?’, $rowArray['id']));

foreach ($langRows as $langRow) {
foreach ($this->lang_fields as $field) {
$colName = $field . ‘_’ . $langRow['code'];
$rowArray[$colName] = $langRow[$field];
}
}
return $rowArray;
}

function buildLangRow($array) {
$rowClass = $this->getRowClass();
return new $rowClass(array(‘table’ => $this, ‘data’ => $array, ‘stored’ => true));
}

public function getInfo($id){
$row = $this->fetchRow($this->select()->where(‘id = ?’, $id));
return $row;
}

public function select()
{
$select = new Zend_Db_Table_Select($this);
$select->setIntegrityCheck(false)
->from($this->_name)
->join($this->getLangTable(),
“{$this->_name}.id = {$this->getLangTable()}.{$this->_sName}_id”,
$this->lang_fields)
->where($this->getLangTable() . ‘.language_id = ?’, Zend_Registry::get(‘language’)->id);
return $select;
}

public function getLangTable()
{
return $this->_name . ‘_lang’;
}

public function countRecords($where = false)
{
$select = new Zend_Db_Table_Select($this);
$select->setIntegrityCheck(false)
->from($this->_name,’COUNT(*) AS num’);

if($where != false) {
$select->where($where);
}

$row = $this->fetchRow($select);
return $row->num;
}

public function delete($where)
{
$tableSpec = ($this->_schema ? $this->_schema . ‘.’ : ”) . $this->_name;

$rows = $this->_db->fetchAll($this->_db->select()
->from($this->_name, array(‘id’))
->where(implode(‘ && ‘, $where)));
$this->_db->delete($tableSpec, $where);

$tableSpec = ($this->_schema ? $this->_schema . ‘.’ : ”) . $this->getLangTable();
foreach ($rows as $row) {
$newWhere[] = $this->_db->quoteInto(“{$this->getLangTable()}.{$this->_sName}_id = ?”, $row['id'], ‘INT(11)’);
}
$this->_db->delete($tableSpec, $newWhere);

return;
}

/**
* Inserts a new row.
*
* @param array $data Column-value pairs.
* @return mixed The primary key of the row inserted.
*/
public function insert(array $insertData)
{
list($data, $langData) = $this->separateLangRows($insertData);

if (is_array($data) && count($data)>0) {
$tableSpec = ($this->_schema ? $this->_schema . ‘.’ : ”) . $this->_name;
$this->_db->insert($tableSpec, $data);
$insertData['id'] = $this->_db->lastInsertId();
}

if (is_array($langData) && count($langData)>0) {
$tableSpec = ($this->_schema ? $this->_schema . ‘.’ : ”) . $this->getLangTable();

foreach ($langData as $language_id=>$data) {
$data[$this->_sName . '_id'] = $insertData['id'];
$data['language_id'] = $language_id;
$this->_db->insert($tableSpec, $data);
}
}

return $this->buildLangRow($insertData);
}

/**
* Updates existing rows.
*
* @param array $data Column-value pairs.
* @param array|string $where An SQL WHERE clause, or an array of SQL WHERE clauses.
* @return int The number of rows updated.
*/
public function update(array $data, $where)
{
list($data, $langData) = $this->separateLangRows($data);

if (is_array($data) && count($data)>0) {
$tableSpec = ($this->_schema ? $this->_schema . ‘.’ : ”) . $this->_name;
$result = $this->_db->update($tableSpec, $data, $where);
}

if (is_array($langData) && count($langData)>0) {
$tableSpec = ($this->_schema ? $this->_schema . ‘.’ : ”) . $this->getLangTable();
$db = $this->getAdapter();

$where[0] = str_replace($this->_name, $this->_sName, $where[0]);
$where[0] = str_replace(‘`.`’, ‘_’, $where[0]);

foreach ($langData as $language_id=>$data) {
$where[1] = $db->quoteInto(“{$tableSpec}.language_id = ?”, $language_id, ‘int(11)’);
$this->_db->update($tableSpec, $data, $where);
}
}

return;
}

private function separateLangRows( array $data) {
$languages = Languages::instance()->getList();
$langData = array();

foreach ($this->lang_fields as $field) {
foreach ($languages as $language) {
$colName = $field . ‘_’ . $language->code;
if (isset($data[$colName])) {
$langData[$language->id][$field] = $data[$colName];
unset($data[$colName]);
}
}
}

return array($data, $langData);
}
}
[/php]

How to use it

To use this class you first setup your model to exted my class.
[php]
class Map_Landmarks_Table extends GSD_Db_TableLang
{
protected $_name = ‘map_landmarks’;
protected $_sName = ‘map_landmark’;

protected $lang_fields = array(‘name’, ‘description’);
[/php]

U should notice that in order for this to work you need to setup 2 extra variable:
1. $_sName this is used to generate the key from the language table. in this case it
would be map_landmark_id.
2. $lang_fields this tells the class witch are the columns in the translated table.

Requirements

First of all you should have a database structure similar to the one i describe here at 4. Coupled Translation table approach

Also you will need to have stored in Zend_Registry the language model.
if you are interested in a method of selecting the language via URL you might be interested in this implementation
Be carefull the registry needs to contain the language mode since i am using the code below to do the join.
[php]
Zend_Registry::get(‘language’)->id
[/php]

Then you will also need to have a Languages Model:
[php]

class Languages extends Zend_Db_Table
{
protected $_name = ‘languages’;
protected $_list = ‘languages’;

public function init()
{
$this->_list = $this->fetchAll();
}

public function getList()
{
return $this->_list;
}

public function findByCode($code)
{
$lang = $this->fetchRow($this->select()->where(‘code = ?’, $code));
return $lang;
}

public function listArray($key = ‘id’)
{
$languages = $this->getList();
$return = array();
foreach ($languages as $language) {
$return[$language->$key] = $language->name;
}
return $return;
}

static public function instance()
{
static $instance;
if (!($instance instanceof Languages)) {
$instance = new Languages();
$instance->init();
}
return $instance;
}
}
[/php]

Hope u are able to find some logic into this extension of Zend_Db_Table ( it took me a while to think it so complicated :) ) ) and await your thought on it.
Cheers.

  • Zh0rzh

    You have something wrong with the source. Poor formatting, “->” instead of “->”, etc.

  • http://www.gsdesign.ro/ Gabi Solomon

    thank you … its was a bad filter that replaced them

  • Jorge

    Good post. Useful.
    I have detected a bug in the insert function. In order to use Zend_Db_Table_Row, Insert must return the pk or array of primary keys. If is an autoincrement, must return lastInsertId of the query. Else, you could end with “Row cannot be refreshed because the parent id is missing” exception.

    In the update method, must return the rows affected, and be sure you use an Array, or get some funny SQLs, at <> because php treats array indexes in strings as the characters.
    So you could add something like:


    if (is_string($where)){
    $where=array($where);
    }

  • http://vaggfaste.com Nicklas

    Thanks for intresting model, could you post it with sources and forms?