Doctrine Translation in leftJoin()
If you use Doctrine, then you probably know how lazy loading can hurt your performance. I carefully craft every query to everything that I need in one shot, but only what I need. One thing that evaded me at first was the i18n part.
I am pleased with the way Doctrine + symfony magically creates all my models and database tables with i18n support. All my relationship names are explicitly defined in the schema.yml file. So, if I want to get all products in a transaction, I can access it via $transaction->Products. When crafting a query, I would say ->leftJoin(‘t.Products’) to make sure to load all the products at the same time as the transaction, potentially saving hundreds of queries later.
Since the i18n relationship is defined in a special way in schema.yml, without specifying a relationship name, I wasn’t sure how to write my Left Join. I looked up the Base[ClassName].class.php file but did not find anything useful. After analyzing all the parent classes, I found it: Translation. It might have seem obvious since the tables are names product_translation, but that element eluded me and I did not find anything in the documentation at the time regarding this.
Now that I can get my product names with ->leftJoin(‘p.Translation’), I cut the number of queries in at least two. Knowing the Translation relationship can have other uses, like counting the number of translations in your code and warning the user about missing translations (no product name in French!). I can also use the Translation relationship to make my search-engine friendly URLs multilingual. I can write about this last one in another post if I get such a request.
The uses for the Translation relationship are infinite!