13.3. Text Information in the Database

A localized application offers different content according to the user's culture. For instance, an online shop can offer products worldwide at the same price, but with a custom description for every country. This means that the database must be able to store different versions of a given piece of data, and for that, you need to design your schema in a particular way and use culture each time you manipulate localized model objects.

13.3.1. Creating Localized Schema

For each table that contains some localized data, you should split the table in two parts: one table that does not have any i18n column, and the other one with only the i18n columns. The two tables are to be linked by a one-to-many relationship. This setup lets you add more languages when required without changing your model. Let's consider an example using a Product table.

First, create tables in the schema.yml file, as shown in Listing 13-6.

Listing 13-6 - Sample Schema for i18n Data, in config/schema.yml

my_connection:
  my_product:
    _attributes: { phpName: Product, isI18N: true, i18nTable: my_product_i18n }
    id:          { type: integer, required: true, primaryKey: true, autoincrement: true }
    price:       { type: float }

  my_product_i18n:
    _attributes: { phpName: ProductI18n }
    id:          { type: integer, required: true, primaryKey: true, foreignTable: my_product, foreignReference: id }
    culture:     { isCulture: true, type: varchar, size: 7, required: true, primaryKey: true }
    name:        { type: varchar, size: 50 }

Notice the isI18N and i18nTable attributes in the first table, and the special culture column in the second. All these are symfony-specific Propel enhancements.

The symfony automations can make this much faster to write. If the table containing internationalized data has the same name as the main table with _i18n as a suffix, and they are related with a column named id in both tables, you can omit the id and culture columns in the _i18n table as well as the specific i18n attributes for the main table; symfony will infer them. It means that symfony will see the schema in Listing 13-7 as the same as the one in Listing 13-6.

Listing 13-7 - Sample Schema for i18n Data, Short Version, in config/schema.yml

my_connection:
  my_product:
    _attributes: { phpName: Product }
    id:
    price:       float
  my_product_i18n:
    _attributes: { phpName: ProductI18n }
    name:        varchar(50)

13.3.2. Using the Generated I18n Objects

Once the corresponding object model is built (don't forget to call symfony propel-build-model and clear the cache with a symfony cc after each modification of the schema.yml), you can use your Product class with i18n support as if there were only one table, as shown in Listing 13-8.

Listing 13-8 - Dealing with i18n Objects

$product = ProductPeer::retrieveByPk(1);
$product->setCulture('fr');
$product->setName('Nom du produit');
$product->save();

$product->setCulture('en');
$product->setName('Product name');
$product->save();

echo $product->getName();
 => 'Product name'

$product->setCulture('fr');
echo $product->getName();
 => 'Nom du produit'

If you'd rather not have to remember to change the culture each time you use an i18n object, you can also change the hydrate() method in the object class. See an example in Listing 13-9.

Listing 13-9 - Overriding the hydrate() Method to Set the Culture, in myproject/lib/model/Product.php

public function hydrate(ResultSet $rs, $startcol = 1)
{
  $this->setCulture(sfContext::getInstance()->getUser()->getCulture());

  return parent::hydrate($rs, $startcol);
}

As for queries with the peer objects, you can restrict the results to objects having a translation for the current culture by using the doSelectWithI18n method, instead of the usual doSelect, as shown in Listing 13-10. In addition, it will create the related i18n objects at the same time as the regular ones, resulting in a reduced number of queries to get the full content (refer to Chapter 18 for more information about this method's positive impacts on performance).

Listing 13-10 - Retrieving Objects with an i18n Criteria

$c = new Criteria();
$c->add(ProductPeer::PRICE, 100, Criteria::LESS_THAN);
$products = ProductPeer::doSelectWithI18n($c, $culture);
// The $culture argument is optional
// The current user culture is used if no culture is given

So basically, you should never have to deal with the i18n objects directly, but instead pass the culture to the model (or let it guess it) each time you do a query with the regular objects.