Query Doctrine/Translatable for translated strings

I’m currently working on a new backend for all our onlineshops that will help us to streamline our workflow so we can get our lovely nutcrackers and cuckoo clocks to the customers even faster than ever before (it’s all about speed, no matter if in the browser or in the warehouse).

As I’m not doing that much backend development these days I gave both Zend Framework and Symfony a try. I went with Symfony (and Rock Hammer, but that’s a story for another day). So far this works out quite well. Symfonys documentation is great, everything (well, almost everything) works as expected and you save a lot of time because you can concentrate on your business logic and don’t have to think about the underlying things as database connections etc.

However, there is one thing with Doctrine and Translatable that took me almost two hours today to figure out.

I have a database table with countrienames in German, english names are stored with the help of Translatable in the default table ext_translations. What I tried to achieve was to get the ID of a country. The only information I got was the name in english:

$oCountry = $oEM->getRepository('W3LShopBundle:Country')->findOneByName($aOrder['countries_name']);

returns no hits. I searched the whole web for a solution as the documentation is not very helpful here. I came with up some solutions that set hints on the query, all of them returning exactly zero rows. I tried to write native SQL, however Doctrine messus up with the backslashes needed for object class. In the end, you get zero results with Doctrine while the same statement works fine in phpMyAdmin (you could work with a like statement on the object class field, but that’s a real performance hog).

Finally, I figured out that you need to set multiple hints.

$oQB = $this->getContainer()->get('doctrine')->getManager()->createQueryBuilder();
$oQuery = $oQB->select('c')->from('W3LShopBundle:Country', 'c')->where('c.name = :name')->getQuery()->setParameter('name', $sCountry);
$oQuery->setHint(\Doctrine\ORM\Query::HINT_CUSTOM_OUTPUT_WALKER, 'Gedmo\\Translatable\\Query\\TreeWalker\\TranslationWalker');
$oQuery->setHint(\Gedmo\Translatable\TranslatableListener::HINT_TRANSLATABLE_LOCALE, 'en_US');
$aCustomerCountry = $oQuery->getResult();

This finally returns results. Maybe this is helpful for anybody else.