How to completely replace a view query?

Question

I have a view whose query I want to completely change. I want to change the select statement, the joins and the where clause. the original query is this:

SELECT node.nid AS nid, node.title AS node_title, field_data_field_location.field_location_tid AS field_data_field_location_field_location_tid, node.created AS node_created, 'node' AS field_data_field_location_node_entity_type, 'node' AS field_data_field_longtext_node_entity_type
FROM 
{node} node
LEFT JOIN {field_data_field_entity_type} field_data_field_entity_type ON node.nid = field_data_field_entity_type.entity_id AND (field_data_field_entity_type.entity_type = :views_join_condition_0 AND field_data_field_entity_type.deleted = :views_join_condition_1)
LEFT JOIN {field_data_field_location} field_data_field_location ON node.nid = field_data_field_location.entity_id AND (field_data_field_location.entity_type = :views_join_condition_2 AND field_data_field_location.deleted = :views_join_condition_3)
LEFT JOIN {node} node_1 ON node.nid = node_1.nid AND (node_1.language = 'bg' AND node.tnid != node.nid AND node.language = 'en')
WHERE (( (field_data_field_entity_type.field_entity_type_tid = :field_data_field_entity_type_field_entity_type_tid ) AND (field_data_field_location.field_location_tid = :field_data_field_location_field_location_tid ) )AND(( (node.status = :db_condition_placeholder_4) AND (node.type IN  (:db_condition_placeholder_5)) AND (node.language IN  (:db_condition_placeholder_6, :db_condition_placeholder_7)) )))
ORDER BY field_data_field_location_field_location_tid ASC, node_title ASC, node_created DESC

and the new query is this:

select CASE when b.node_title is not null then b.nid else a.nid end as nid,
CASE when b.node_title is null then a.node_title else b.node_title end as node_title,
CASE when b.node_title is not null then b.field_data_field_location_field_location_tid else a.field_data_field_location_field_location_tid end as field_data_field_location_field_location_tid,
CASE when b.node_title is not null then b.node_created else a.node_created end as node_created,
'node' AS field_data_field_location_node_entity_type, 'node' AS field_data_field_longtext_node_entity_type
from 
(SELECT node.tnid, node.nid AS nid, node.title AS node_title, field_data_field_location.field_location_tid AS field_data_field_location_field_location_tid, node.created AS node_created, 'node' AS field_data_field_location_node_entity_type, 'node' AS field_data_field_longtext_node_entity_type
FROM 
node node
LEFT JOIN field_data_field_entity_type field_data_field_entity_type ON node.nid = field_data_field_entity_type.entity_id AND (field_data_field_entity_type.entity_type = 'node' AND field_data_field_entity_type.deleted = '0')
LEFT JOIN field_data_field_location field_data_field_location ON node.nid = field_data_field_location.entity_id AND (field_data_field_location.entity_type = 'node' AND field_data_field_location.deleted = '0')
WHERE   field_data_field_entity_type.field_entity_type_tid = '818' 
AND field_data_field_location.field_location_tid = '408' 
AND node.status = '1'
AND node.type IN  ('data_entity') 
AND node.language = 'en') a LEFT OUTER JOIN 
(SELECT node.tnid, node.nid AS nid, node.title AS node_title, field_data_field_location.field_location_tid AS field_data_field_location_field_location_tid, node.created AS node_created, 'node' AS field_data_field_location_node_entity_type, 'node' AS field_data_field_longtext_node_entity_type
FROM 
node node
LEFT JOIN field_data_field_entity_type field_data_field_entity_type ON node.nid = field_data_field_entity_type.entity_id AND (field_data_field_entity_type.entity_type = 'node' AND field_data_field_entity_type.deleted = '0')
LEFT JOIN field_data_field_location field_data_field_location ON node.nid = field_data_field_location.entity_id AND (field_data_field_location.entity_type = 'node' AND field_data_field_location.deleted = '0')
WHERE   field_data_field_entity_type.field_entity_type_tid = '818' 
AND field_data_field_location.field_location_tid = '408' 
AND node.status = '1'
AND node.type IN  ('data_entity') 
AND node.language = 'bg') b
ON a.nid= b.tnid

I tried with views_pre_execute hook and with query_alter view with no luck... The first attemt is using https://drupal.org/node/409808 $view->build_info['query'] = MY QUERY; which throws error:Call to a member function addMetaData() on a non-object

The attempt with query alter is a dissaster...I cannot make it work..

Answer 1

As the others say you can use hook_views_query_alter in your custom module and this is Sample of using it :

function alterform_views_query_alter(&$view, &$query) {
  switch($view->name) {
    case 'myViewName':  
      echo 'test test'; // test       
      $query->orderby[0] = "FIELD(node.type, 'story', 'page', 'productTypeC', 'productTypeD') ASC";
      $query->orderby[1] = "node_title ASC";
      break;
  }
}

Answer 2

hook_views_query_alter

Use the hook_views_query_alter(&$view, &$query) to alter the present 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.

Answer 3

I wasn't able to solve my problem by tampering the query. In order to solve my "language fallback" problem, I used the hook_views_post_execute(&$view) and parsed the results in order to remove the nodes not needed. In more details... My query fetched the nodes in the english (default site language) and in the language selected by a UI dropdown. Then in the views_post_execute hook I removed what not needed.

switch ($view->current_display) {           
    case "my_case_block":
      if ($language->language != 'en') {
        $results = $view->result;
        $parsed_results = array();
        $index = 0;
        $source_node = node_load(arg(1));
        $source_node_parent_nid = $source_node->tnid;
        foreach ($results as $result) {
          $result_node = $result->_field_data['nid']['entity'];
          if (!empty($result_node)) {
            if ($result_node->tnid == $source_node_parent_nid){
              continue;
            }
            if ($result_node->language == 'en') {
              $translations = translation_node_get_translations($result_node->nid);
              if (!empty($translations) && count($translations) > 0 && array_key_exists($language->language, $translations)){
                continue;
              }
            }
          }
          $parsed_results[$index] = $result;
          $index++;
        }
        // HERE I REPLACE THE RESULT with the parsed one
        $view->result = $parsed_results;
      }
      break;
  }

Thank you all for your help and suggestions