Multiple join query

Question

i have a custom module and am using some queries inside, but am not getting the proper output

 $query = db_select('node', 'N_en')->extend('PagerDefault');
 $query->addField('N_en', 'nid', 'en_nid');
 $query->addField('N_en', 'title', 'en_title');
 $query->addField('N_en', 'type', 'en_type');
 $query->addField('N_ar', 'nid', 'ar_nid');
 $query->addField('N_ar', 'title', 'ar_title');
 $query->addField('N_fr', 'nid', 'fr_nid');
 $query->addField('N_fr', 'title', 'fr_title');
 $query->addField('N_de', 'nid', 'de_nid');
 $query->addField('N_de', 'title', 'de_title');

 $query->leftJoin('node', 'N_ar', 'N_en.nid=N_ar.tnid AND N_ar.language=:lang', array(':lang' => 'ar'));
 $query->leftJoin('node', 'N_fr', 'N_en.nid=N_fr.tnid AND N_fr.language=:lang', array(':lang' => 'fr'));
 $query->leftJoin('node', 'N_de', 'N_en.nid=N_de.tnid AND N_de.language=:lang', array(':lang' => 'de'));

 $query->where("N_en.status = 1  AND  N_en.type NOT IN ('catering', 'mobile','news','webform') 
    AND N_en.tnid != 0  AND N_en.language = 'en' " );


$bool = $query->execute();

Am not getting the correct output. Arabic title is coming for French and German.

stdClass Object
(
   [en_nid] => 18
   [en_title] => About Us
   [en_type] => about_us
   [ar_nid] => 1693
   [ar_title] => Arabic About us 
   [fr_nid] => 1693
   [fr_title] => Arabic About us 
   [de_nid] => 1693
   [de_title] => Arabic About us 
)
stdClass Object
(
   [en_nid] => 133
   [en_title] => Awards and Achievements
   [en_type] => about_us
   [ar_nid] => 1708
   [ar_title] => Arabic test
   [fr_nid] => 1708
   [fr_title] =>Arabic test
   [de_nid] => 1708
   [de_title] =>Arabic test
 )

Answer 1

I would suspect that query placeholders get overridden. All thou not pretty and most likely theres different method for adding dynamic replacements, try this.

$query->leftJoin('node', 'N_ar', 'N_en.nid=N_ar.tnid AND N_ar.language=:lang_ar', array(':lang_ar' => 'ar'));
$query->leftJoin('node', 'N_fr', 'N_en.nid=N_fr.tnid AND N_fr.language=:lang_fr', array(':lang_fr' => 'fr'));
$query->leftJoin('node', 'N_de', 'N_en.nid=N_de.tnid AND N_de.language=:lang_de', array(':lang_de' => 'de'));

Answer 2

Debugging queries is fortunately very easy. After building your query, before executing, use this:

drupal_set_message((string) $query);

You can then compare that with the query string you're expecting it to produce, and find the problem quickly.