Drupal | Multiple authors for an article and Views with one to many relationships. Part two

In Part one I described the challenge of creating a view which displays a list of Articles (nodes) which have a multi-value reference field pointing to one or more Writers / Authors (users). We want each article to be listed only once regardless of how many writers it has and we want several fields from the related writers displayed below the article title etc.

In more general terms, we want a view with a one to many relationship to display the  parent data one row at a time along with a collection of related child rows. The pager must be related to the parent rows.

I solved this problem a couple of years ago with a Drupal 6 site by writing my own module which used SQL queries to directly query the database. I did this on two queries. The first query retrieved the page of articles only. My PHP code then looped through those results collecting the nids (node ids). The second query retrieved the writers for those nodes. The SQL included WHERE n.nid IN(...). Inside the parentheses was a comma separated list of nids. PHP code then associated the returned users with nodes returned in the first query based on the nid. The combined data was outputed with a theme template. I like that way of retrieving the data. It's clean, logical and is easy to optimize with good use of indexes etc. I've used that technique in many non-Drupal and non-PHP applications.

Now I am creating a Drupal 7 site with the same requirement. When my attempts with views described in part one failed I started several times down the same path of writing my own module but stopped every time because it just seemed like a bad idea. I really wanted to use Views because it's such an incredible module. If at all possible, I wanted to avoid accessing the database with my own SQL. The Drupal 7 schema is more complex than Drupal 6 so there is a greater possiblity of getting it wrong. Views knows how to access the database and comes with lots of other flexibility.

Then it struck me. I could do the same sort of thing as I did with my Drupal 6 module but using Views for the queries. The solution is really quite simple. It requires some code in a hook implementation and a custom Views template.

I made two views. Both are primarily based on nodes (i.e., Content on that first wizard page). Within the view, they both Show Fields rather than Content.

The first or "parent" view does not have a relationship to the writers. It just returns the fields we want from the Articles. There is no duplication problem because it does not have a one to many relationship with anything. The pager works correctly. This view has a Page display.

The other view also starts with nodes but it does have a relationship defined to the users representing the writers. The only field we return from the Articles is nid. We include the first name, last name, image and any other user defined fields we want from the Writer / user. This query may of course return multiple writers with the same nid but that's okay. It has no pager and is not defined for a page or a block so it only has the Master display. The only filter it has that really matters is a contextual filter by nid which allows multiple values. It probably also makes good security sense to also have fixed filters for the usual stuff like content type and published etc.

The key to all this is merging the results. That's where we need some code. hook_views_post_execute seems to be a good place to merge the results. I put this in my module called views_demo

function views_demo_views_post_execute(&$view) {

  // my view is called articles_list
  // process it if we're not editing it and the result array is not empty

  if ($view->name == 'articles_list' && !$view->editing && $view->result) {

   // build the array $nids to use as a quick way of looking up the row in
   // $view->result in nid

    $nids = array();
    $idx = 0;
    foreach($view->result as $row) {
      $nids[$row->nid] = $idx;
      $row->writers = array();

   // the other view is called article_writers
   // call it with a parameter of a comma separated list of nids

    $params = implode(',', array_keys($nids));
    $writers = views_get_view_result('article_writers', null, $params);
    foreach($writers as $writer) {
      $idx = $nids[$writer->nid];

      // attach the writers to the correct node.
      $view->result[$idx]->writers[] = $writer;

The data returned by a view is returned in $view->result which is an array of objects, one for each item or row. views_get_view_result runs a view and returns the result in an array of objects similar to $view->result. I guess the other stuff in $view gets added later. In the code above, we add an extra property to each row in $view->result. That property is an array of zero or more writers.

After executing that code an article row in $view->result which has two writers looks like this.

The writer data that my code has inserted is not going to magically appear on the page. For that we need to customize the view's Row style output template. In my case it means copying views-view-fields.tpl.php from the Views module to views-view-fields--articles-list.tpl.php in my theme. It ended up like this:

<?php /* get the writers array */
$writers = $view->result[$view->row_index]->writers;

<?php print $fields['title']->content; ?> <?php print $fields['created']->content; ?>
<?php print $fields['body']->content; ?>
<div class="clearfix">
<?php foreach($writers as $writer): ?>
<div class="writer">
<a href="/user/<?php print $writer->users_field_data_field_writers_uid; ?>"><?php print render($writer->field_field_image[0]['rendered']); ?></a>
<?php print render($writer->field_field_first_name[0]['rendered']); ?> <?php print render($writer->field_field_last_name[0]['rendered']); ?>
<?php endforeach; ?>

The fields from the articles view appear in the $fields collection which is the usual place where the template reads it but the data I inserted from the writers view is still where I put it in the items of $view->result.

The $view variable is available in the row style template. Luckily, the property $view->row_index tells us which row we are displaying. That allows me to get the writers array in the first line of code. As you can see from the krumo output above, the writer fields contain a render array. That's convenient because code like this outputs it correctly.

print render($writer->field_field_image[0]['rendered']);


Some problems, opportunies and final thoughts

Well, that's about it. I'm quite happy with how it works. I have all the flexibilty of views along with an efficient (IMHO) non-klugy way of getting the data.

One potential issue is that the data from the second view is only in a convenient ready to print format for user defined fields (i.e, "CCK" type fields) of the entity. Those fields have a nice render array ready to process with the render function. The standard fields on an object do not provide that. I discovered that when I initially used the standard user Picture for a user. All I get from views_get_view_result() for that field from is the file id. I assume Views does its magic later before the data would normally appear in the $fields array in the row style template. I "fixed" this problem by defining an Image field and using that instead. I think the user Picture is kind of a legacy feature these days anyway. In my production site, I don't really have fields defined in User. I'm using the Profile2 module and my writers' images and other data are fields defined in a profile. That means my second view has two relationships, one to the writer and another to the profile but the concept is the same.

It would be nice if there was a way to really merge the results seamlessly so that the writer data ends up as a array which is one 'field' in the $fields array in the template. I haven't dug into it further to try to do that or know if it's even possible.

It seems like it would be possible to write a module to do all this in a generic way. I can visualize an admin page where you tell it what view is to be used for the "parent" data and then define one or more "child" views and what fields to use for the relationship. I may have a go at it if and when time permits.

I just found the views_field_view module. I haven't tried it but it looks good and I think it can achieve the same result. The only issue I see is one of efficiency and performance. I assume it runs the second "field view" once for every row of the main view. That's got to be quite a performance hit if the page has lots of items. But... with good caching, especially with anonymous users so the page caching works, maybe that's not such an issue. I'd prefer to get all the child records with one database query.

Thanks for any feedback.