0

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][0]['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.

Thanks.

  • 1
    In a query you would use a JOIN to relate the records of two tables on a field value. However I would suggest that you use Views to display a list of Staff using a contextual filter on the Account ID. Much simpler than re-inventing the wheel. – Triskelion May 24 '13 at 15:07
  • Thanks for all your helpful comments. I would love to use Views rather than reinventing the wheel if I can. But I am not sure how in views to combine the two content types. For example in Views if I make a page for the Charities filtered by content type = "charity". Then create an attachment (Attachment settings - Attach to: Page, Attach position: After) which I intend to display the Staff with. I can see how to add a contextual filter on the Account ID, but I can't remove the content type = "charity", or add a filter for content type = "staff" without it affecting both the view and the page. – Paul Trotter May 28 '13 at 8:39
  • Thanks again for your helpful comments. I received an excellent answer to an earlier question I had posted that achieves what I need to via views. The suggestions below look great, but as views is more straightforward I'm going to go with that. I really appreciate the time you have taken to help with this. Best, Paul – Paul Trotter May 31 '13 at 10:20
2

Scott's answer and Triskelion's comment are both spot on, but there are also a couple of other solutions that are worth mentioning.

  1. Query the database for some node IDs that match a condition, load them using node_load_multiple(), and inspect the field data:

    $nids = db_select('node', 'n')
      ->fields('n', array('nid'))
      ->condition('status', 1)
      ->condition('type', 'salesforce_contacts')
      ->condition('n.title', 'Abouzeid')
      ->orderBy('title', 'ASC')
      ->execute();
    
    $nodes = node_load_multiple($nids);
    
    foreach ($nodes as $node) {
      $title = $node->title;
      $created = $node->created;
      // etc.
    
      if ($field_items = field_get_items('node', $node, 'field_account_id')) {
        $account_id = $field_items[0]['value'];
      }
    }
    
  2. Use an EntityFieldQuery to query the nodes. This method has the advantage of being able to add conditions based on fields, as well as just the columns available in the node table.

    $query = new EntityFieldQuery();
    $query->entityCondition('entity_type', 'node')
      ->propertyCondition('type', 'salesforce_contacts')
      ->propertyCondition('status', 1)
      ->propertyCondition('title', 'Abouzeid')
      ->propertyOrderBy('title')
    
    $results = $query->execute();
    if (!empty($results['node'])) {
      $nodes = node_load_multiple(array_keys($results['node']));
    
      // Loop through the nodes as in the above example.
    }
    
1

First you need to join the table with the data you need. This will add the account id information to the nodes you select.

I'd do a left join like:

$query->leftJoin('TABLE', 'TABLESHORTNAME', 'TABLESHORTNAME.FIELDNAME = node.FIELDNAME');

The 3rd argument sets the relationship for the join. In this case the entity id must be the same value in both tables.

Now you can use the account id in your fields output or your conditions.

I find it helpful to browse the database and find all the tables and fields you'll need first.

You may need to add a condition to limit the type of nodes you return since many types of nodes can have the same field.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.