6.x-3.x docs.php hook_views_query_alter(&$view, &$query)
7.x-3.x views.api.php hook_views_query_alter(&$view, &$query)

Alter the query before executing the query.

This hook should be placed in MODULENAME.views.inc and it will be auto-loaded. MODULENAME.views.inc must be in the directory specified by the 'path' key returned by MODULENAME_views_api(), or the same directory as the .module file, if 'path' is unspecified.

Parameters

$view: The view object about to be processed.

$query: An object describing the query.

See also

hook_views_query_substitutions()

Related topics

1 invocation of hook_views_query_alter()
views_plugin_query_default::alter in plugins/views_plugin_query_default.inc
Let modules modify the query just prior to finalizing it.

File

./views.api.php, line 1070
Describe hooks provided by the Views module.

Code

function hook_views_query_alter(&$view, &$query) {
  // (Example assuming a view with an exposed filter on node title.)
  // If the input for the title filter is a positive integer, filter against
  // node ID instead of node title.
  if ($view->name == 'my_view' && is_numeric($view->exposed_raw_input['title']) && $view->exposed_raw_input['title'] > 0) {
    // Traverse through the 'where' part of the query.
    foreach ($query->where as &$condition_group) {
      foreach ($condition_group['conditions'] as &$condition) {
        // If this is the part of the query filtering on title, change the
        // condition to filter on node ID.
        if ($condition['field'] == 'node.title') {
          $condition = array(
            'field' => 'node.nid',
            'value' => $view->exposed_raw_input['title'],
            'operator' => '=',
          );
        }
      }
    }
  }
}

Comments

Ghostthinker’s picture

/**
 * Implementation of hook_views_query_alter
 * @param type $view
 * @param type $query 
 */
function mymodule_views_query_alter(&$view, &$query) {

  //simple example: change the order of the master display
  //if you want to do it only  on a certain display add something 
  // like  "&&$view->current_display == 'panel_page_1'"
  if ($view->name == 'VIEWNAME') {

    //to find out what the fieldname ist: use devel and add your desired field as 
    //first filter ( =>orderby[0]). install devel and uncomment the next line
    //dpm($view->query->orderby)

    $view->query->orderby[0]['field'] = "CASE FIELD_NAME WHEN 'apple' THEN 1 WHEN 'zebra' THEN 2 WHEN 'banna' THEN 3 ELSE 4 END";
  }
}
Pere Orga’s picture

If you are looking for this function in Drupal 8, see hook_views_query_alter(ViewExecutable &$view, QueryPluginBase &$query)

fortmac’s picture

As recommended in this post, it would be beneficial to check out this documentation regarding views_plug_in_query_default.

monaw’s picture

I tried to put my function (mymodule_views_query_alter) in mymodule.views.inc as per instruction at the top of the function documentation, but it didn't work...my hook never got executed.

I disabled and re-enabled my module several times, cleared the cache and even added "files[] = mymodule.views.inc" in mymodule.info but nothing worked.

So instead, I put my function in mymodule.module, restarted my module and that worked.

Hope this will save others some headache...

monaw’s picture

I forgot to say that I'm using D7 and Views 3.7, in case that matters...

frankyn’s picture

Hi,

I had the same issue and I just cleared my cache. I personally used Drush, but you should be able to do it from the Admin side inside Drupal.

Have a nice day
Frank N.

gantenx’s picture

To get it work, you need to implement hook_views_api().

AlxVallejo’s picture

Well that predefines the path, so that goes against the description (?)

AlxVallejo’s picture

Nevermind, that's an example path, not a default path.

skorzh’s picture

Another reason can be if you use Search API module, in this case hook_search_api_views_query_alter will work instead.

pdcarto’s picture

This hook will be invoked even if 'Disable SQL rewriting' is enabled for the view. That's not necessarily obvious. In my case, this was a good thing.

karlos007’s picture

i think this option only skips tags - for example node_access

pearl.liang’s picture

Thanks Permalink. I have the same situation. I have hook_views_api in .inc file but still not working. I follow Permalink's advice and it works.
I suggest Views 3 author update the docutmentation here or fix the bug:
https://api.drupal.org/api/views/views.api.php/function/hook_views_query...

screon’s picture

Its sounds very silly, but make sure your view will actually generate results.

I couldn't get the hook to work because my view didn't return any results thanks to a faulty contextual filter.

brijesh.stepin@gmail.com’s picture

Hi,
When I create a views and when I found event for filter using particular year it will get "no result found." in drupal 6. So can anybody help me for out of these solutions.

I can get result only filter using year.

Wilson_of_Prussia’s picture

Hello. I tried the foreach loop suggested in the notes but for whatever reason it wasn't affecting the condition array. So I took the longhand method and did a while loop (below) and it worked perfectly. Any idea why this might be? I've experienced it in another module as well.

$i=1;
while(isset($query->where[$i])){
  $j=0;
  while(isset($query->where[$i]['conditions'][$j]['field'])){
   	if($query->where[$i]['conditions'][$j]['field'] == 'targeted.field'){
      		$query->where[$i]['conditions'][$j]['value'] = $new_value;								
       	}
	$j++;
  }
$i++;
}
DarkteK’s picture

How can I remove one table which is in my query with a LEFT JOIN???

rajveergang’s picture

Can you please comment your mysql query. If i am thinking right than write views_query_alter
then after checking the LEFT JOIN condition you can execute db_query(drop table TABLE_NAME);