You are here

Using Hook Views Query Alter

Building a website I recently ran into an issue where I had HTML search facets interacting with Drupal views. The user would click on the facet and it would filter the view depending on the argument. I ended up implementing hook_views_query_alter. To make sure the values I wanted to filter on were in the view, I added the search facets into the view as fields. This automatically joins the necessary tables so that you don’t have to. Implementing hook_views_query_alter requires two parameters, view and query, both are passed by reference. Since this hook is called for every view the first thing I did was check that I was altering the right query by adding an if statement that checks the view name. After that you're free to modify the query as you wish. In my example, I looped through the search facets and added them as where statements and arguments to the query.


function search_views_query_alter(&$view, &$query) {
  if($view->name == 'example_view_name'){
    foreach($parsed_url AS $key => $value){ 
      where[0]['clauses'][] = 'profile_values_profile_' . $key . '.value IN ("%s")'; 
      where[0]['args'][] = $value; 
    }
  }
} 

One of the search facets was a taxonomy filter which the user was allowed to multi select. In order to get the view to grab the correct values, I had to add join clauses to dynamically create term_node table aliases. This is done by adding a join clause to the table_queue and tables array elements within the query object. I was able to just copy the existing term_node table_queue and tables array elements because I only wanted to create different aliases. Then I incremented the existing term_node table count within the tables array. If you wanted to create a new table join dynamically, the same approach is used but the table_queue and tables definitions need to be created by hand.

foreach($parsed_url AS $key => $value) { 
  $vid = _search_get_vocabulary_id(str_replace('_', ' ', $key)); 
  $tid = _search_get_term_by_vocabulary($vid, $value); 
  $table_term_node_join = $query->table_queue['term_node']; 
  $query->table_queue['term_node_' . $count] = $table_term_node_join; 
  $query->table_queue['term_node_' . $count]['alias'] = 'term_node_' . $count; $query->tables['term_node']['count']++; 
  $query->where[0]['args'][] = $tid->tid; 
  $query->where[0]['clauses'][] .= 'term_node_' . $count . '.tid = %d'; $count++; 
} 

 The query table_queue and tables array elements looks like so:

 

Table Queue:
'table_queue' => array (
  'term_node' => array (
    'table' => 'term_node',
    'num' => 1,
    'alias' => 'term_node',
    'join' => views_join::__set_state(array(
      'definition' => array (
      'left_field' => 'vid',
      'field' => 'vid',
      'table' => 'term_node',
      'left_table' => 'node',
    ),
    'extra_type' => 'AND',
    'table' => 'term_node',
    'left_table' => 'node',
    'left_field' => 'vid',
    'field' => 'vid',
    'type' => 'LEFT',
    'adjusted' => true,
    )
  )

Tables:
'tables' => array (
    'node' => array (
      'node' => array (
      'count' => 1,
      'alias' => 'node',
      ),
    'term_node' => array (
      'count' => 1,
      'alias' => 'term_node',
    )
  ),
);

 

In my book this makes views more comfortable to use because it gives me more control over them. This along with a view preprocessor allows me to style and output pretty much anything I want within a view.

Building Rich Internet Apps with Drupal & HTML5
Learn the power of combining Drupal + HTML5 + CSS3 + JS
SHARE SOCIAL
Lead Developer
Brandon is one of our Lead Developers, he is passionate about the open source community, rocks at building websites and can work in multiple Drupal versions.Brandon’s IT career began with a degree in Computer Science from Thompson Rivers University in Kamloops, BC. Following graduation he worked...→ More about Brandon
Lead Developer

Brandon is one of our Lead Developers, he is passionate about the open source community, rocks at building websites and can work in multiple Drupal versions.

Brandon’s IT career began with a degree in Computer Science from Thompson Rivers University in Kamloops, BC. Following graduation he worked for Acro Media Inc where he started programming websites in C++.

Bucket List Accomplishments: Visiting Laguna Seca

Current Playlist Includes: JRE - Joe Rogan Experience

Best Way to Spend a Sunny Day in Vancouver: Snowboarding, hiking and enjoying time with his family

Find out more about Brandon

Appnovation Technologies