Query with db_select() to select node by id and join field and terms

Question

I am trying to write a query using db_select that selects a particular node by id, and then joins it's field_data_field_projectresources column and also selects the terms from taxonomy (because this is a term_reference field)

I have tried numerous attempts but I am finding myself stuck (I didn't even get to the part about joining the dr_taxonomy_term_data table)

 $query = db_select('node','n')
  ->condition('n.nid','5')
  ->fields('n')
  ->fields('p');
 $query->Join('field_data_field_projectresources','p','p.entity_id = n.nid');
 $result = $query->execute();

 while($record = $result->fetchAssoc()) {
   print_r($record);
 }

Any help appreciated, Thanks

Answer 1

I hope this works for you:

 $query = db_select('node','n');
 $query->join('field_data_field_projectresources','p','p.entity_id = n.nid');
 $query->fields('n',array('yourfieldnames'))
       ->fields('p',array('yourfieldnames'))
       ->condition('n.nid','5');
 $result = $query->execute();

Here is another answer that will help you very much.

Check out documentation for 'join' on drupal.org.