Looks up the faculty for a program and it's upcoming program_instance

program_instance_faculty_info

  // use the following to get the list of programs with the list of keywords(field_tag) and the list of program categories(field_program_category)
  
  $programs = array();

  $query = db_select('node', 'n');
  $query->fields('n', array('nid', 'status', 'type', 'created'));

  if (!empty($tag_ids)) {
    $query->innerJoin('field_data_field_tag', 't', "n.nid = t.entity_id AND t.entity_type = 'node'");
    $query->fields('t', array('field_tag_tid'));
    $query->condition('t.field_tag_tid', $tag_ids, 'IN');
  }

  if (!empty($category_ids)) {
    $query->innerJoin('field_data_field_program_category', 'pc', "n.nid = pc.entity_id AND pc.entity_type = 'node'");
    $query->fields('pc', array('field_program_category_tid'));
    $query->condition('pc.field_program_category_tid', $category_ids, 'IN');
  }

  $query->condition('n.type', 'program');
  $query->condition('n.status', '1');

  $results = $query->execute();
  foreach ($results as $record) {
    $programs[$record->nid] = (empty($programs[$record->nid])) ? 1 : $programs[$record->nid] + 1;
  }
  
  ...
  
  then with each program do something like this...
  
  ...
  
  // Lookup the program instances for a given program
  
  $query = db_select('node', 'n');
  $query->fields('n', array('nid', 'status', 'type'));
  $query->join('field_data_field_program', 'fp', "n.nid = fp.entity_id AND fp.entity_type = 'node' AND fp.deleted = '0'");
  $query->fields('fp', array('entity_id', 'entity_type', 'deleted', 'field_program_target_id'));
  $query->join('node', 'nodefp', 'fp.field_program_target_id = nodefp.nid');
  $query->fields('nodefp', array('nid'));
  $query->join('field_data_field_instance_date', 'fid', "n.nid = fid.entity_id AND fid.entity_type = 'node' AND fid.deleted = '0'");
  $query->fields('fid', array('field_instance_date_value'));
  $query->join('field_data_field_display_on_finder', 'dof', "n.nid = dof.entity_id AND dof.entity_type = 'node' AND dof.deleted = '0'");
  $query->fields('dof', array('field_display_on_finder_value'));
  $query->condition('nodefp.nid', $program_nid);
  $query->condition('n.status', '1');
  $query->condition('n.type', 'program_instance');
  $query->condition('dof.field_display_on_finder_value', '1');
  $query->orderBy('fid.field_instance_date_value', 'ASC');
  $query->range(0,1);
  $results = $query->execute();
  
  $faculty_directors = array();
  foreach ($results as $record) {
    $instance_wrapper = entity_metadata_wrapper('node', $record->nid);
    foreach ($instance_wrapper->field_faculty_directors as $key => $fci) {
      $view = $fci->view('faculty_leadership');
      $id = $fci->getIdentifier();
      if (isset($view['field_collection_item'][$id])) {
        $faculty_directors[$id] = $view['field_collection_item'][$id];
        $faculty_directors[$id]['#weight'] = $key;
      }
    }
  }