I created and maintain a webapp which accepts Applications for a specific Program at my school. If students are interested in attending the summer-time program they must enlist their teachers, counselors and other community advocates to complete a Reference Application advocating their placement into the program. In the existing database structure I was finding it difficult to build a report that shows student applications and if they’ve had 1 submission, 2 submission, are all submissions received, etc. I’ve recently amended the application to make this report and it required I use a Computed Field.

Existing Site Entity Structure

Existing Drupal site entity relationship

In the existing website I created. A possible student enrollment is represented as a Node in Drupal. This node contains a Field Collection whose fields represent a community advocate (reference) that is supposed to complete the webform questionnaire on behalf of the student.

The status of the completion of this webform, per advocate, is tracked with a custom Relation in Drupal that records the webform submission id (SID). This relation in Drupal also flattens much of the Node and field collection data as attached fields to the Relation simply for convenience of data access (data duplication is sometimes a “Good Thing”). The relation entity also contains a unique access key for use by the advocate to complete the specific questionnaire.

Problems with the Existing Entity Structure

With the above relationships between the various Drupal entities the information exists to create many different types of reports, including the report we want. But, it’s not terribly simple to get at the necessary information across the various tables using typical Drupal tools such as Views. For instance I can ask “How many submissions have been recorded for X001232?”, but I then want additional information about pieces of other entities.

It’s possible for instance to try to use Views Relationships and Aggregation to generate our report. I went ahead and installed Views Aggregator Plus and generated the following report in 1 of many attempts at creating this report (what can I say, I hadn’t had my coffee yet for the day).

View Page configuration settings and table aggregation settings:

View Page configure for Fields and Layout using Aggregation from Views Aggregator Plus.

Views Aggregator Plus table settings.

The final output of this View using this aggregation approach:

Final View’s report output using Aggregator Plus. It worked! … Or did it.

But, this approach has problems:

1. The click sortable Views table headers expect a database column value to be sorting on. Sorting on an aggregation value doesn’t really work across the full dataset.
2. We can see the values we want on the table (submitted 1, submitted 2 responses or submitted 3 responses). But we cannot add an Exposed Filter on aggregated values. They are derived values when the SQL statement is processed.

New Site Entity Structure

In order to work around the issues outlined above and make this report more manageable and simple to implement we need to take a step back and look at the data values we have available to us. What if we change the entity structures to be:

Let’s modify our existing structure by adding a new computed field.

See how we added something called computed submission count to the Student Application content type? Wouldn’t that make our reporting much easier? I think so. This field is a Computed Field in Drupal 7, a contributed community module.

Computed Field is a developer centric module which is described as:

Computed Field is a very powerful field module that lets you add a custom “computed fields” to your content types. These computed fields are populated with values that you define via PHP code. You may draw on anything available to Drupal, including other fields, the current user, database tables, you name it. (Feeling the power yet? 🙂 ) You can also choose whether to store your computed field values in the database with other content fields, or have them “calculated” on the fly during node views. (Although you should note that Views use requires database stored values.) This field is literally the Swiss Army knife of fields. So start cooking up your PHP based values!

So I added a computed field to the student application record using the following noteworthy field settings:

• Recalculate the field value every time.
• Store value in the database
• Index computed values in the database
• Data Type: INT
• Number of values: 1

I then added the following code to the fields Computed Code (PHP) setting:

// I wasn't sure what the $entity value contained or where
// the NID value was at. Devel's dd() function creates an
// output log in my /tmp folder with a var_dump of the value.
dd($entity);
$nid = $entity->nid;
$efq = relation_query('node', $nid);
$efq->addTag('RmpReferencesRelations');
$efq->propertyCondition('relation_type', 'summer_session_user_has_survey');
$result = $efq->execute();
$relation_ids = array_keys($result);


$sum = 0;
if (count($relation_ids)) {
  $query = db_select('field_data_field_rel_webform_submitted', 'r')
      ->fields('r', 'field_rel_webform_submitted_value')
      ->condition('r.entity_id', $relation_ids, 'IN');
  $alias = $query->addExpression('SUM(r.field_rel_webform_submitted_value)', 'sum_of_submissions');
  $sum = $query->execute()->fetchField();
}
$entity_field[0]['value'] = $sum;

The above code is fairly straightforward it is 2 steps:

1. Use the Relation module API and an EntityFieldQuery to get the relation IDs that contain the given student application Node IDs.
2. Using the RIDs from (1) use an SQL query with an inline SUM expression to compute a running total of submissions which have been submitted (the sql column defaults to 0 on creation, and is toggled to 1 per record on submission).

Now we can create a simple view of the desired data. But what if our site contains hundreds of submissions already? Since we set recalculate the value every time, any node_save() will update the field from the PHP code (nice). A simple for loop over Nodes of our content type and saving the nodes will re-compute the value. But an easier approach is to use Computed Field Tools to update all Nodes using the Batch API:

Computed Field update all computed fields values admin batch page.

Then our field is updated for all existing nodes. Great! Our View is now very simple and simply contains a new Field and a Filter for the field:

Configuration for simple view of student applications, sortable via number of submitted responses.

Example final view working as desired.

At a later time we may add additional details to this report (like list the associated Advocate records) or Ajax links — but that’s for another time!

Looking for quality Drupal Hosting? Look no further than Arvixe Web Hosting!