\hook_views_query_alter
function
Alter the query before it is executed.

Alter the query before it is executed.

Comments

charginghawk’s picture

If you want to order by a formula (ie, use characters that otherwise won't make it past escapeField (like spaces, for instance)), you have to use addField or addOrderBy. See http://drupal.stackexchange.com/a/200662/44595

vladm’s picture

You must implement this hook inside my_module.views_execution.inc. It won't work inside my_module.module file.

lykyd’s picture

I'm using this hook in a mymodule.views.inc file and it only gets fired the first time I load the views after a "drush cr". The view is Restful type generating Json.

EDIT ==

I just renamed the file mymodule.views.inc to mymodule.views_execution.inc and now the hook gets fired each time properly. Thanks for the life saving info ;)

joeuk31’s picture

I'm getting the following error when trying to get this function working

Recoverable fatal error: Argument 1 passed to MYMODULE_views_query_alter() must be an instance of ViewExecutable, instance of Drupal\views\ViewExecutable given

Any ideas guys?

Edit:

I removed ViewExecutable and QueryPluginBase from the arguments and it seems to have worked. Any ideas why? is this is the correct way, am I missing something?

MmMnRr’s picture

Drupal 8 is focused on classes rather than arrays as Drupal 7. You must use the specific classes that are used on this hook in your ".module" file:

use Drupal\views\ViewExecutable;
use Drupal\views\Plugin\views\query\QueryPluginBase;
...
function MYMODULE_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  ...
}

The error message you got is because you did not specify which Drupal class.

patilvishalvs’s picture

This should be documented.

Sudishth’s picture

/**
* Implements hook_views_query_alter().
*/

function mymodule_views_query_alter(Drupal\views\ViewExecutable $view, Drupal\views\Plugin\views\query\Sql $query) {
    if ($view->id() == 'my_views' && $view->getDisplay()->display['id'] == 'attachment_1') {
        // Traverse through the 'where' part of the query.
        foreach ($query->where as &$condition_group) {
            foreach ($condition_group['conditions'] as &$condition) {
                if (strpos($condition['field'], 'groups_field_data_id')) {
                    $db = \Drupal::database();
                    $result = $db->select('group_content_field_data', 'gcfd');
                    $result->fields('gcfd', array('gid'))
                        ->condition('gcfd.entity_id', \Drupal::currentUser()->id());
                    $val = $result->execute()->fetchCol();
                    $condition['value'][':groups_field_data_id[]'] = $val;
                }
            }
        }
    }
}
Devendra Mishra’s picture

Thank for helping. Actually the implementation should be like

function mymodule_views_query_alter($view, $query) {
    if ($view->id() == 'my_views' && $view->getDisplay()->display['id'] == 'attachment_1') {
        // Traverse through the 'where' part of the query.
        foreach ($query->where as &$condition_group) {
            foreach ($condition_group['conditions'] as &$condition) {
                if (strpos($condition['field'], 'groups_field_data_id')) {
                    $db = \Drupal::database();
                    $result = $db->select('group_content_field_data', 'gcfd');
                    $result->fields('gcfd', array('gid'))
                        ->condition('gcfd.entity_id', \Drupal::currentUser()->id());
                    $val = $result->execute()->fetchCol();
                    $condition['value'][':groups_field_data_id[]'] = $val;
                }
            }
        }
    }
}

Using mymodule_views_query_alter(Drupal\views\ViewExecutable $view, Drupal\views\Plugin\views\query\Sql $query) sometime through warning messages.

andriy_novak’s picture

function hook_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
   if ($view->id() == 'my_views' && $view->getDisplay()->display['id'] == my_display') {      
       $configuration = array(
          'type'       => 'INNER',
          'table'      => 'taxonomy_index',
          'field'      => 'nid',
          'left_table' => 'node_field_data',
          'left_field' => 'nid',
          'operator'   => '=',
        );

        $join = \Drupal\views\Views::pluginManager('join')
          ->createInstance('standard', $configuration);
        $rel = $query->addRelationship('taxonomy_idx', $join, 'node_field_data');
        $query->addTable('taxonomy_index', $rel, $join, 'taxonomy_idx');
        $query->addWhere('', 'taxonomy_idx.tid', $tids, 'IN');
  }
}
Nick Hutu’s picture

Can this be used to make contextual filters use a LIKE operator versus the default equals operator? And if I am not using any custom "mymodule" module, where should I add the function?
Thanks!

HAL 9000’s picture

jungle’s picture

$query->where

Should be

$query->getWhere

ssibal’s picture

The second argument of this hook is QueryPluginBase $query.
Which doesn't implement functions such as "where", "addRelationship" etc.
Yet everyone using this function. This is so "cheap" solution. You assume you will get an SQL instance (child of QueryPluginBase) because you have MySQL in your settings. But in OOP you break the fundamentals like this. Couldn't this be solved somehow prettier?

vlalieu’s picture

I was using the code of "Devendra Mishra" until version Drupal 8.3.7 . When using Drupal 8.4 I got a strange error when saving node with the selected Item in the select list. "This entity cannot be referenced"
Rather than changing the value in the pre-existing query condition, I have added a Where condition to the query:
$query->addWhere('conditions','groups_field_data_group_content_field_data.id',(string)$teamid,'=');
That solved the problem.