StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POComplex Query in Doctrine 2
text
Body
copied!<p>I have a multilingual database(inspired from <a href="https://stackoverflow.com/questions/316780/schema-for-a-multilanguage-database">here, the 3rd option</a>.)</p> <p>My database schema looks like this: </p> <pre><code> Article: (id, fk_name, fk_description, fk_reference, fk_um) Translation: (id) TranslationText: (id, fk_translation, fk_language,text) Language (id, name) Article --(M:1)--> Translation --(1:M)--> TranslationText --(M:1)--> Language </code></pre> <p>I'm using Symfony 2 with Doctrine and i need a report for all Articles in a certain language(passsed as parameter).</p> <p>The problem appers when an <strong>Article</strong> has a <strong>Name</strong>, <strong>Description</strong> or <strong>Reference</strong> in a particular language but doesn't have <strong>Um</strong>(at least not in the language requested).</p> <p>My best aproach so far is this SQL that i can't implement in Doctrine 2 due to the lack of nested joins.(<strong>language_id = 28</strong>)</p> <pre><code> select ing.* , tx_name.text, tx_description.text, tx_reference.text, tx_um.text from Article ing left join (Translation t_name left join TranslationText tx_name on t_name.id = tx_name.fk_translation and tx_name.fk_language = 28) on ing.fk_name = t_name.id left join (Translation t_description left join TranslationText tx_description on t_description.id = tx_description.fk_translation and tx_description.fk_language = 28) on ing.fk_description = t_description.id left join (Translation t_reference left join TranslationText tx_reference on t_reference.id = tx_reference.fk_translation and tx_reference.fk_language = 28) on ing.fk_reference = t_reference.id left join (Translation t_um left join TranslationText tx_um on t_um.id = tx_um.fk_translation and tx_um.fk_language = 28) on ing.fk_um = t_um.id ; </code></pre> <p>Is there anyway to simplify the query and to make it work in Doctrine's DQL ?</p> <p>Kind Regards,</p> <p>Dan Cearnau</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload