Drupal Technical

[Drupal] How to join multiple database tables using db_select?

Deepa.n | | 1 min read

Before we discuss on how to to join multiple database tables using db_select in Drupal 7, let me tell you that we can add as many joins as we want and most methods such as, 'fields', 'conditions', joins etc. can be called multiple times. However, one thing may be worth mentioning is that the call to join() needs to be separate. It should not be "chained", meaning that one should use this,

$query = db_select('comment', 'c');
$query->innerjoin('users', 'u', 'c.uid = u.uid');
$query->innerjoin('field_data_comment_body', 'fc', 'fc.entity_id = c.cid');

instead of the following,

$query = db_select('comment', 'c')
  ->innerjoin('users', 'u', 'c.uid = u.uid')
  ->innerjoin('field_data_comment_body', 'fc', 'fc.entity_id = c.cid');

in order to make it work. Because it returns the name for the table alias and not the query object.

Now, just call join() twice to join three database tables. Easy!

$query = db_select('comment', 'c');
$query->innerjoin('users', 'u', 'c.uid = u.uid');
$query->innerjoin('field_data_comment_body', 'fc', 'fc.entity_id = c.cid');
$query->fields('c', array('cid', 'pid', 'nid', 'uid' 'subject', 'created', 'name', 'mail', 'homepage', 'name', 'status'));
$query->fields('u', array('signature', 'signature_format', 'picture', 'data'));
$query->fields('fc', array('comment_body_value'));
$query->condition('cid', array(1,2,5), 'IN');
$result = $query->execute();
while ($comment = $result->fetchAssoc()) {
  print_r($comment);
}

Now if you are curious to know how the query string with actual arguments looks like, then check this out.

If you would like to hire our experts, please let us know. Also we can provide a wide range of Drupal services to help you manage your Drupal websites.

 

 

Related Topics