How do I sort an internationalized i18n table with symfony and doctrine?

Question

I would like to display a list of records from an internationalized table using sfDoctrinePager. Not all the records have been translated to all the languages supported by the application, so I had to implement a fallback mechanism for some fields (by overriding the getFoo() function in the Bar.class.php, as explained in another post here). I have different fallback list for each culture. Everything works fine until when it comes to sorting the records in alphabetical order.

I'm sorting the records at the SQL (Dql) level, by adding an ->orderBy('t.name') to the query:

    $q = Doctrine::getTable('Foo')
        ->createQuery('f')
        ->leftJoin('f.Translation t')
        ->orderBy('t.name')

But here come the troubles: the list gets not sorted correctly, regardless of the active culture. I get rather better results when I limit the translations to the active culture, like this:

->leftJoin('f.Translation t WITH lang = ?', $request->getParameter('sf_culture');

Then the sorting is correct, as far as all the translations exist for the active culture. If a translation does not exist and I have to take the name from the fallback language, the record will be displayed at the very beginning of the list (I understand this happens because the value for the current culture is null). My question is: is there a best practice for getting internationalized fields (needing fallbacks) sorted correctly with doctrine and sfDoctrinePager? Thank you in advance.

Answer 1

1. Create the DQL query that returns both primary and fallback language: ->innerJoin('f.Translation ft WITH ft.lang IN(?)', array(array('pl', 'en'))
2. Make sure that primary language is sorted before fallback one: ->orderBy('FIELD(ft.lang, "pl", "en")
3. Now you should be able to add ->orderBy('ft.name') and it should works as expected.

Use sfDoctrinePager::setQuery() or sfDoctrinePager::setTableMethod() to pass query for the pager.

Answer 2

I'd second Crozin's answer, although I'm suprised that the sorting is not working properly (assuming you i18n tables have been set up correctly under symfony rules).

As an alternative approach to fallbacks in some cases, if translations do not always exist, or you're unable or unwilling to return two results, and if the amount of data contained in the translations table is not very large, you could also duplicate the data in the database for each language, with translations over-written for those fields that they are available for. This way a single language criterion will always return a result - either default or translated. It's not the cleanest solution in terms of storing data, but it might suit some specific cases.