I am trying to build a custom drupal module to return the 'staff' that work at a specific charity when you are on the page of the charity. Both the charities and the staff members exist as separate content types. These content types are updated dynamically from a CRM system. The charities and the staff members both have a common field 'Account ID'.
I can get the account id of the charity whose page we are viewing:
//Get Account ID of Charity for page we are on $charity_account_id = $node->field_member_account_id[LANGUAGE_NONE]['value'];
I can query the database and return only the contacts of the staff members type ('salesforce_contacts') and optionally limit it by the title of the returned results (e.g: n.title','Abouzeid')
//Use Database API to retrieve the appropriate contact(s) $query = db_select('node', 'n') ->fields('n', array('nid', 'title', 'created')) ->condition('status', 1) //Published. ->condition('type', 'salesforce_contacts') // content type is salesforce_contacts. ->condition('n.title','Abouzeid','=') // I can limit the returned results by a specific title ->orderBy('title', 'ASC') //Most recent first. ->execute(); return $query;
I need to know how to limit the returned results by the value of a specific cck field from the staff member content type ('salesforce_contacts'). This will allow me just to return the contacts for the charity we are looking at.
I have tried adding the drupal machine name for the field (or the mySQL database table name) to the field array as in below but both of these throw a PDOException error.
->fields('n', array('nid', 'title', 'created', 'field_account_id'))
I would really appreciate some help with this. This is my first Drupal database query and I am pretty new to DB queries in general. I have tried reading through the documentation but am struggling to where to look to get ideas as to how to go about achieving what I need to.