Complex Query in Doctrine 2

Question

I have a multilingual database(inspired from here, the 3rd option.)

My database schema looks like this:

 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

I'm using Symfony 2 with Doctrine and i need a report for all Articles in a certain language(passsed as parameter).

The problem appers when an Article has a Name, Description or Reference in a particular language but doesn't have Um(at least not in the language requested).

My best aproach so far is this SQL that i can't implement in Doctrine 2 due to the lack of nested joins.(language_id = 28)

 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
     ;

Is there anyway to simplify the query and to make it work in Doctrine's DQL ?

Kind Regards,

Dan Cearnau

Answer 1

I'll try to write your query without subselects(subjoins): The indented join are at the same level of dbms p.o.v, but for us are dependent on previous tables.

select ing.*
    , tx_name.text 
    , tx_description.text
    , tx_reference.text
    , tx_um.text
from Article ing 
  left join Translation t_name on ing.fk_name = t_name.id 
     left join TranslationText tx_name
             on (t_name.id = tx_name.fk_translation
             and tx_name.fk_language = 28)
  left join Translation t_description on ing.fk_description = t_description.id
     left join TranslationText tx_description
             on (t_description.id = tx_description.fk_translation
             and tx_description.fk_language = 28)
  left join Translation translation_reference on ing.fk_reference = translation_reference.id
     left join TranslationText tx_reference
             on (translation_reference.id = tx_reference.fk_translation
             and tx_reference.fk_language = 28)
  left join Translation translation_um on ing.fk_um = translation_um.id
     left join TranslationText tx_um
             on (translation_um.id = tx_um.fk_translation
             and tx_um.fk_language = 28);

Also, I think your model is overnormalized. For me, this is better:

Article:          (id, another_columns)
TranslationText:  (fk_article_id, fk_language,name_text, description_text, reference_text, um_text)
Language          (id, name)   

As I see now, this is the model indicate in the most voted answer for the question indicated in your link