2

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..

4
  • 3
    Looks like XY problem to me. Probably you have problem X, and you think Y is the way to solve it, so you ask us about Y. And maybe it would be simpler to answer to the original X issue? Maybe what you want to achieve, your actual problem, does not require abusing views so much?
    – Mołot
    Nov 29, 2013 at 13:58
  • Explain the requirement.
    – ARUN
    Nov 29, 2013 at 15:26
  • The truth is that what I am trying to achieve is something like language fallback. If a node translation exists in the user language I want to fetch it otherwise I want to fetch the default language. My view works as described below.: if a node has English and Bulgarian translations and my user language is Bulgarian it fetches all the English and the Bulgarian nodes. If the user language selected is French it fetches only the English nodes that are not translated and it does not fetch the English node of the node that has Bulgarian translation. Is what I am trying to say clear?
    – Maggie
    Nov 29, 2013 at 15:29
  • Shall I continue trying to make it work with views or shall I try implementing a custom page with db_select to fetch my data? By the way I am using drupal 7. Thanks for your time and answers.
    – Maggie
    Nov 29, 2013 at 15:37

3 Answers 3

2

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;
  }
}
1

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.