CODEWITHSUNDEEP
phpdrupal-7
Arun
Arun

Reputation: 3721

Drupal JOIN works with db_query, but not in db_select

In my drupal code, I wrote the below code and it works fine with few records as result

Code

$result = db_query("SELECT f1 
            FROM table1 as n 
            JOIN table2 as om ON n.f1 = om.f1 
            JOIN table3 as fs ON n.f1 = fs.f1 
            JOIN table4 as sm ON fs.f2 = sm.f1 
            WHERE om.f2 = :f2 
            AND om.f3 = 'type' 
            AND sm.f2 = 'yes'  
            AND sm.f4 = :f4 
            AND n.type = 'test'", array(':f2' => $f2,':f4' => $f4));
 $record = $result->fetchAll();

Then I converted the above code to drupal code generator format like below but it returns empty record as result

Code

$record = db_select('table1', 'n');
$record -> fields('n', array('f1'))
  -> condition('om.f2', $f2, '=')
  -> condition('om.f3', 'type', '=')
  -> condition('sm.f2', 'yes', '=')
  -> condition('sm.f4', $f4, '=')
  -> condition('n.type', 'test', '=');
$record -> join('table2', 'om', 'n.f1 = om.f1');
$record -> join('table3', 'fs', 'n.f1 = fs.f1');
$record -> join('table4', 'sm', 'fs.f2 = sm.f1');
$record -> execute()
  -> fetchAll();

I couldn't find any problem with the code. Am I doing something wrong?

Upvotes: 2

Views: 577

Answers (1)

Moby M
Moby M

Reputation: 910

Please try this,

<?php
$record = db_select('table1', 'n');
$record -> join('table2', 'om', 'n.f1 = om.f1');
$record -> join('table3', 'fs', 'n.f1 = fs.f1');
$record -> join('table4', 'sm', 'fs.f2 = sm.f1');
$result = $record
  ->fields('n', array('f1'))
  -> condition('om.f2', $f2, '=')
  -> condition('om.f3', 'type', '=')
  -> condition('sm.f2', 'yes', '=')
  -> condition('sm.f4', $f4, '=')
  -> condition('n.type', 'test', '=');
  ->execute();
  ->fetchAll();

foreach ($result as $row) {
  // Do something with $row.
}

Upvotes: 3

Related Questions