Rewrite a Views query

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); 
 
 
 
 
 
 
 
<div style="width: 377px;display:block;margin:0 auto;"><a href="/sites/<a href="http://www.raisedeyebrow.com/files/wordpress/2010/04/views_query_krumo.gif"><img">www.raisedeyebrow.com/files/wordpress/2010/04/views_query_krumo.gif"><img</a> class="size-full wp-image-846" title="Anatomy of a View Query" src="/sites/<a href="http://www.raisedeyebrow.com/files/wordpress/2010/04/views_query_krumo.gif"">www.raisedeyebrow.com/files/wordpress/2010/04/views_query_krumo.gif"</a> alt="Anatomy of a View Query" width="367" height="373" /></a><p>Anatomy of a View Query</p></div>