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

Why exactly do you need these nested joins? Isn't the result the same if you remove the parenthesis and move the on clauses to the right place? — greg0ire, Nov 11 '11 at 08:21

score 1 · Accepted Answer · edited May 23 '17 at 12:02

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

Complex Query in Doctrine 2

1 Answers1