How do I sort an internationalized i18n table with symfony and doctrine?
- by Maurizio
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.
