Create Setup class with possibility to create tables with translatable fields

[stalniy]

It's necessary to create Setup (or some behavior) class that would give api to easy implement modules with fields that must be available on different languages. This class can create some additinal tables automatically for example i have table "news" and i want that fields "title" and "description" will be available for a few languages (the api of business models and collection must be the same as for non-translatable table). Table "news" has few fields. There are id, title, description, is_active, created_at.

In the install file of module i create a ddl table object and set some parameter (or behavior object) that responsible for the creation of the additional table with template name %base_tablename%_translation (e.g. news_translation). This table contains all translatable fields (e.g. title, description) id and store_id. Id field is the same that is in base table (e.g. news). Also you need to add primary index for (id, storeid).

For example,

$table = $installer->getConnection()
    ->newTable($installer->getTable('news'))
    ->addColumn('block_id', Varien_Db_Ddl_Table::TYPE_SMALLINT, null, array(
        'identity'  => true,
        'nullable'  => false,
        'primary'   => true,
        ), 'News ID')
    ->addColumn('title', Varien_Db_Ddl_Table::TYPE_TEXT, 255, array(
        'nullable'  => false,
        ), 'News Title')
    ->addColumn('description', Varien_Db_Ddl_Table::TYPE_TEXT, '2M', array(
        ), 'News Description')
    ->addColumn('creation_time', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
        ), 'Block Creation Time')
    ->addColumn('update_time', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
        ), 'Block Modification Time')
    ->addColumn('is_active', Varien_Db_Ddl_Table::TYPE_SMALLINT, null, array(
        'nullable'  => false,
        'default'   => '1',
        ), 'Is Block Active')
    ->setComment('CMS Block Table');

// Add Table Behavior
$table->addBehavior(new TranslatableBehavior(array('title', 'description'));


$installer->getConnection()->createTable($table);

This code must generate the following SQL:

CREATE TABLE `news` (
 `id` smallint(6) NOT NULL AUTO_INCREMENT,
 `creation_time` datetime DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 `is_active` tinyint(1) NOT NULL DEFAULT '1'
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='CMS Blocks'

CREATE TABLE `news_translation` (
 `id` smallint(6) NOT NULL,
 `title` varchar(255) not null,
 `description` text,
 `store_id` smallint(5) unsigned NOT NULL,
 PRIMARY KEY (`id`,`store_id`),
 KEY `FK_CMS_BLOCK_STORE_STORE` (`store_id`),
 CONSTRAINT `FK_CMS_BLOCK_STORE_BLOCK` FOREIGN KEY (`id`) REFERENCES `news` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `FK_CMS_BLOCK_STORE_STORE` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='CMS Blocks to Stores';

To work with models withous changing api:

// collection
$collection = new TranslatableCollection(); 
$collection->setStoreId(Mage::app()->getStore()->getId())->load();

// item
$item = new TranslatableModel();
$item->setStoreId(Mage::app()->getStore()->getId())->load($itemId);

echo $item->getTitle();
echo $item->getDescription();

It would look like a Doctrine I18n Behavior - http://docs.doctrine-project.org/projects/doctrine1/en/latest/en/manual/behaviors.html#i18n .

[magento-team]

Looks like this message is sending over and over ~ can you stop it?

-----Original Message-----
From: Sergiy Stotskiy [mailto:reply@reply.github.com]
Sent: Friday, June 29, 2012 3:44 PM
To: DL-Magento-Mage2-Team
Subject: [magento2] Create Setup class with possibility to create tables with translatable fields (#31)

It's necessary to create Setup (or some behavior) class that would give api to easy implement modules with fields that must be available on different languages. This class can create some additinal tables automatically for example i have table "news" and i want that fields "title" and "description" will be available for a few languages (the api of business models and collection must be the same as for non-translatable table). Table "news" has few fields. There are id, title, description, is_active, created_at.

In the install file of module i create a ddl table object and set some parameter (or behavior object) that responsible for the creation of the additional table with template name %base_tablename%_translation (e.g. news_translation). This table contains all translatable fields (e.g. title, description) id and store_id. Id field is the same that is in base table (e.g. news). Also you need to add primary index for (id, storeid).

For example,

$table = $installer->getConnection()
    ->newTable($installer->getTable('cms_block'))
    ->addColumn('block_id', Varien_Db_Ddl_Table::TYPE_SMALLINT, null, array(
        'identity'  => true,
        'nullable'  => false,
        'primary'   => true,
        ), 'Block ID')
    ->addColumn('title', Varien_Db_Ddl_Table::TYPE_TEXT, 255, array(
        'nullable'  => false,
        ), 'Block Title')
    ->addColumn('identifier', Varien_Db_Ddl_Table::TYPE_TEXT, 255, array(
        'nullable'  => false,
        ), 'Block String Identifier')
    ->addColumn('content', Varien_Db_Ddl_Table::TYPE_TEXT, '2M', array(
        ), 'Block Content')
    ->addColumn('creation_time', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
        ), 'Block Creation Time')
    ->addColumn('update_time', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
        ), 'Block Modification Time')
    ->addColumn('is_active', Varien_Db_Ddl_Table::TYPE_SMALLINT, null, array(
        'nullable'  => false,
        'default'   => '1',
        ), 'Is Block Active')
    ->setComment('CMS Block Table');

// Add Table Behavior
$table->addBehavior(new TranslatableBehavior(array('title', 'content'));


$installer->getConnection()->createTable($table);

This code must generate the following SQL:

CREATE TABLE `news` (
 `id` smallint(6) NOT NULL AUTO_INCREMENT,  `creation_time` datetime DEFAULT NULL,  `update_time` datetime DEFAULT NULL,  `is_active` tinyint(1) NOT NULL DEFAULT '1'
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='CMS Blocks'

CREATE TABLE `news_translation` (
 `id` smallint(6) NOT NULL,
 `store_id` smallint(5) unsigned NOT NULL,  PRIMARY KEY (`id`,`store_id`),  KEY `FK_CMS_BLOCK_STORE_STORE` (`store_id`),  CONSTRAINT `FK_CMS_BLOCK_STORE_BLOCK` FOREIGN KEY (`id`) REFERENCES `news` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,  CONSTRAINT `FK_CMS_BLOCK_STORE_STORE` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='CMS Blocks to Stores'; ```

To work with models withous changing api:
```php
// collection
$collection = new TranslatableCollection(); $collection->setStoreId(Mage::app()->getStore()->getId())->load();

// item
$item = new TranslatableModel();
$item->setStoreId(Mage::app()->getStore()->getId())->load($itemId);

echo $item->getTitle();
echo $item->getDescription();

It would look like a Doctrine I18n Behavior - http://docs.doctrine-project.org/projects/doctrine1/en/latest/en/manual/behaviors.html#i18n .
Then

---

Reply to this email directly or view it on GitHub:
#31

[colinmollenhour]

@stalniy, why not just write a (edit: community) Magento module that adds support for Doctrine?

[stalniy]

@colinmollenhour
Hmm... Maybe because supporting of Doctrine it's a bad idea?

Edit:
Doctrine supporting it's not a thing that was described. It will make working with multistore values much easier. There are a lot of tables in Magento that has suffix something like "_store" (attach some entity to specific store). I just propose to automate this kind of work

[colinmollenhour]

I fully agree it is a bad idea. I wouldn't personally do it and I definitely don't think the core team should do it. But this feature is very much ORM and Magento is not an ORM-heavy framework (thank god!). If you really want ORM-like features in Magento (as is evidenced by this issue you created) I thought you might like the idea of a community extension to support Doctrine in Magento rather than add these features into Magento core.

[stalniy]

I have described this thing at http://freaksidea.com/php_and_somethings/show-24-internatsionalizatsiia-v-magento . There is also realization for Magento 1.6 and it will work also for Magento2 (but the usage is different). Link to module is http://freaksidea.com/uploads/php/magento/i18n/FI_I18n.tar.bz2

[magento-team]

@stalniy
Thank you for providing comprehensive source code examples and articles.
The proposed approach of Doctrine-like I18n Behavior does allow to simplify and streamline management of translatable entities.
However, as @colinmollenhour already pointed out, currently Magento does not offer ORM, only Database Abstraction Layer. While support of multiple RDBMS is declared as a goal of the Magento 2 project, no ORM features are planned for 2.0.

[stalniy]

It's just terms. I did not suggest to create some kind of ORM or something like that. I suggested to use the similar design pattern for your DDL table class in addition to make it more flexible, easier extendable and more useful

[magento-team]

Closing as "won't fix".