Symfony i18n table: Ways to get fallback default value?

Question

Hi,

I'm building a city names table in MySQL with about 10K rows to be a Symfony i18n table. Basically, the default culture is en_US and every city in the table is initially of this culture. Over time, I'd like to add to the table only those cities that might have an alternative name in a different language, such as "London (en_US) / Londres (es_ES)", without having to replicate all the city data for each language in separate tables.

Now, from what I understand, Symfony won't automatically pick a fallback default city name if a translation for it doesn't exist. So I need to craft a solution to do the following:

If translation exists, select it.... if not, select the default en_US city name.

So far it seems that I should use COALESCE. However, as I'm not very familiar with it, I'm wondering if there's any drawbacks to using it a lot? I'm asking this because I'd need to include it every city query which is a very frequent action on my site.

Would be grateful for any views, or even suggestions for doing things in a better way. Thanks.

Answer 1

Try something like this:

$q = Doctrine_Query::create()
     ->from('Cities c')
     ->leftJoin('c.Translation ct WITH lang IN(?)', array(array('es_ES', 'en_US')))
     ->execute();

Answer 2

What you could also do is overwrite de getName() method in the City Model (or create a new one, like getNameWithDefault()). Something like this could also work:

public function getName()
{
  // Assuming that $this->name will give us the data for the current locale
  return ($this->name == NULL)? $this->Translation->en_US->name: $this->name;
}

On your database query, of course, you will have to load both Translations every time. If you are showing the main contents in spanish you will need to get both data, as pointed before by Crozin.

Answer 3

If you store available languages in a different table, you can do something like this. So it will return default culture name or if it is NULL, it will return a first which will be found.

public function getName($culture = null)
{
  if($this->getCurrentCitiesI18n($culture)->getName() != NULL)
    return $this->getCurrentCitiesI18n($culture)->getName();
  else
  {
    foreach (LanguagesPeer::getAvailablelanguages() as $lang)
    {
      if($this->getCurrentCitiesI18n($lang->getIsoCode())->getName() != NULL)
        return $this->getCurrentCitiesI18n($lang->getIsoCode())->getName();        
    }
  }
}