Raised Eyebrow Blog

Recently, due to Views lack of ability to OR it’s filters together and due to some serious constraints in relation to arguments and relationships, I found myself needing to figure out how to rewrite the SQL of a Drupal view so that I could get the records that I needed.

It seems like there are two ways to do this. You can either rewrite the SQL directly, or try to hack the query object that views provides.

A quick Google search and I ended up on Drupal.org

Which then led me to the Views documentation

From here I was able to deduce that the

hook_views_pre_execute()

hook was what I needed.

I created my module and added the hook:

/**
 * Implementation of hook_views_pre_execute()
 *
 **/
function other_views_pre_execute(&$view) {
  switch($view->name) {
    // If it's my view
    case 'my_view':
      switch($view->current_display) {
        // If it's the block on the author page
	case 'block_5':
	  // Set the extra argument
	  $view->build_info['query_args'][2] = $view->build_info['query_args'][1];
	  // Rewrite the query
	  $view->build_info['query'] = "SELECT ... ";
	  break;
       }
   }
}

Lets step through this:

function other_views_pre_execute(&$view) {

We need to make sure the word hook is replaced with the name of our module, in this case it’s called ‘other’

switch($view->name) {
  // If it's my view
  case 'my_view':

Each view is represented by a unique name. We need to modify the query for a certain view, not all of them, in this case the view is called ‘my_view’.

switch($view->current_display) {
  // If it's the block on the author page
  case 'block_5':

Views can have multiple displays, such as blocks, pages and attachments. In this case we want to target the block display with the name/id ‘block_5′

// Set the extra argument
$view->build_info['query_args'][2] = $view->build_info['query_args'][1];
// Rewrite the query
$view->build_info['query'] = "SELECT ... ";
break;

In this case our query is going to have an extra argument added to the query. These arguments are an array $view->build_info['query_args']. They appear in the form ‘%s’ and ‘%d’ in the query text, like this:

WHERE (node.status <> 0) AND (node.type in ('%s'))
AND ((node_node_data_field_publication_centre_authors.title = '%s') OR (node_node_data_field_publication_first_author.title ='%s'))

In this case the first ‘%s’ = $view->build_info['query_args'][0], the second one = $view->build_info['query_args'][1] and so on.

And that’s it. Your query is rewritten. One thing to watch our for, do not add any extra fields in the SELECT statement, this can cause all sorts of crazy problems.

The alternative to using the pre_execute hook is to use the query_alter hook.

This seems a lot trickier and even Earl Miles himself hates this hook, so I suggest staying away from it, but if you’re interested in what it looks like here’s a quick Krumo screenshot of what it looks like. This was achieved using the devel module along with the kpr() command. So

kpr($view->query); 

Anatomy of a View Query

I’m working on a site right now that has a Publications content type, which in turn has a multiple value CCK text field for Author. If more than one author was input I needed them to display as a comma separated list in a view. How do you go about modifying this? My first stop was the theming info link in Views, and that helped me narrow it down to a template file to use, views-view-field.tpl.php(). But that file didn’t allow me to modify the individual fields without getting a whole load of other HTML in the bargain.

< ?php
// $Id: views-view-field.tpl.php,v 1.1 2008/05/16 22:22:32 merlinofchaos Exp $
 /**
  * This template is used to print a single field in a view. It is not
  * actually used in default Views, as this is registered as a theme
  * function which has better performance. For single overrides, the
  * template is perfectly okay.
  *
  * Variables available:
  * - $view: The view object
  * - $field: The field handler object that can process the input
  * - $row: The raw SQL result that can be used
  * - $output: The processed output that will normally be used.
  *
  * When fetching output from the $row, this construct should be used:
  * $data = $row->{$field->field_alias}
  *
  * The above will guarantee that you'll always get the correct data,
  * regardless of any changes in the aliasing that might happen if
  * the view is modified.
  */
?>
< ?php print $output; ?>

Using the brilliant “Theme developer” extension of the Devel module I was able to pin point the theme function – theme_content_view_multiple_field that would allow me to modify the actual value of the field without the wrapper div’s etc (I’m not getting into the views divitis argument.

Here’s a quick snippet of code to place in your template.php file of your theme to allow you to modify multiple value fields:

function custom_theme_content_view_multiple_field($items, $field, $values) {
  $output = '';
  switch($field['field_name']) {
    // If this is the author field then we need to comma separate the authors
    case 'field_author':
      $output .= '<div class="field-item">'. implode(', ', $items) .'</div>';
      break;
 
    default:
      foreach ($items as $item) {
        if (!empty($item) || $item == '0') {
	  $output .= '<div class="field-item">'. $item .'</div>';
	}
      }
      break;
  }
  return $output;
}

I used a switch statement because I have a lot of other multiple fields that need to be themed in different ways and it’s nicer than a whole load of if statements. Also switch provides a default state if a condition isn’t met.

As Melanie mentioned, we just launched a site for Pivot Legal, and a successful launch it was too. I thought I’d take a moment to give a quick breakdown of some of the tricks and wizardry we incorporated on that site.

Valid Code

For starters, we succeeded in getting most of the site to be valid XHTML, of course with Drupal there are parts where client content entry will cause some warnings and Drupals own internals will also do the same, but most of the pages on this site, within our control, are valid.

Lawyer Blogs, Resources and Events

This was the first site that we have implemented multi user blogging and user content categorization, so it was a learning experience for us. Each lawyer has their own page, in this case, that page is the default user profile page. However it’s not just your ordinary user profile page, it’s a themed user profile page. To allow us to theme this page we simply called the following function in template.php

/**
* Catch the theme_profile_profile function, and redirect through the template api
*/
function phptemplate_user_profile($account, $fields) {
	return _phptemplate_callback('user_profile', array('account'=>$account, 'fields'=>$fields));
}

Then create a template file called user_profile.tpl.php, here’s a snippet from that file:

$lawyer_profile = '<div id="lawyer_profile">'. chr(10)
   .'<h2>'. $account->profile_fullname .'</h2>'. chr(10)
   .'<h3>'. $area_of_law .'</h3>'. chr(10)
   .'<p>'. $account->profile_biography .'</p>'. chr(10)
   .'</div>'. chr(10);
// Photo, if any:
  if ($account->picture) {
    $lawyer_photo = theme('image', 	$account->picture, $account->profile_fullname .'\'s photo', $account->profile_fullname);
  }

The Blog, Resource and Events blocks on the lawyer page are simply View blocks that have the username as an argument. To achieve this we used the Usernode module to convert user profiles to nodes so that we could use their properties in views. I believe Views 2 in Drupal 6 will handle all this without the need to this module.

Username funkiness

As there are lots of users creating lots of content Drupal spits out their username attached to the piece of content (e.g “Posted by colin on Feb 26, 2009″). However on this site we wanted to show their full name and link that back to their profile page, again a simple theming function achieved this

function custom_theme_username($object) {
  if ($object->uid && $object->name) {
    // Load user so that we can get the full profile name instead of the short username
    $user = user_load(array('uid' =>$object->uid));
    $name = $user->profile_fullname;
    // If it's the "Pivot Legal LLP" user, go to the 'about-us/' page instead:
    $path = ($name != 'Pivot Legal LLP' ? 'user/'. $object->uid : 'about-us/');
 
    if (user_access('access user profiles')) {
      $output = l($name, $path, array('title' => t('View user profile.')));
    }
    else {
      $output = check_plain($name);
    }
  }
  else if ($object->name) {
    // Sometimes modules display content composed by people who are
    // not registered members of the site (e.g. mailing list or news
    // aggregator modules). This clause enables modules to display
    // the true author of the content.
    if ($object->homepage) {
      $output = l($object->name, $object->homepage);
    }
    else {
      $output = check_plain($object->name);
    }
    $output .= ' ('. t('not verified') .')';
  }
  else {
    $output = variable_get('anonymous', t('Anonymous'));
  }
  return $output;
}

Searching

It was a requirement to have a searchable directory of free legal resources on this site. We built this using a content type and Views, along with the Views Fast Search module. This allows you to create a view which acts like a search but uses view Filters to restrict the search. Robert Douglass, one of the famed Lullabots has written a great tutorial on how to set it up.

How did you get the search box on the home page?

This is pretty easy. Go ahead and create your Fast Search that we mentioned above. Then create a block and paste the following code into the body of the block, making sure to include the php tags and to set the input format to PHP:

  < ?php
  // Get the view
  $view = views_get_view('directory_search');
  // Get the filters ( in this case the search box) - this is returned as a form
  $form = drupal_retrieve_form('views_filters', $view);
  // When you submit the form make sure we go to the view page
  $form['#action'] = url($view->url);
  // Set your form up correctly in drupal
  drupal_process_form('views_filters', $form);
  // Spit the form out to screen
  return drupal_render_form('views_filters', $form);
  ?>

Of course this method can be applied to any view filters.

Views, views and more views

The rest of the site is mostly views and view blocks with some serious argument handling. The rotating images on the homepage are achieved using a simple content type for the image and then using a view to randomly load one of those pieces of content.

If you have any questions about any of the other functionality on the site or on anything I’ve menitoned above please feel free to post a comment.

We’ve come across an issue quite a few times during the development/build of a site where a client wants to include some content above or below a view and wants to have the ability to edit that content. There are a number of ways to achieve this functionality:

1. You could simply use the “Header” field in the create/edit view screen and place some text in there. However, that does not have a nice WYSIWYG editor, one could be assigned but it’s not ideal.
2. You could create a block and place it in a region above the view. Again, unless you have a WYSIWYG Editor set up for blocks, this is not perfect.
3. You could, in some way, add a view to the bottom of a page – ah!, that’s it.

The caveat with 1 and 2 above is that it requires giving the site editor/client access to views and blocks, in a lot of cases this is way too much control and will usually result in problems. 3 is the ideal solution. Once I figured this out, I began to develop the functionality: I created a Select CCK select field for all page content types which pulled a list of all available views by using the views_get_all_views() function. This field stored the name of the view, I then used the name of this view in page.tpl.php to load the view via the function views_get_view(). It started to get a little messy, so I decided to start building a module, only to find that one already existed.

It’s called Viewfield and does exactly what it says on the tin. It allows you to add a CCK field which is a list of views to choose from. The view will then display on a page in whatever position and format you choose. It works great.

You can see it in action on this page from the BCNDP website.