3

How do I change the JOIN type in a SQL query that Views creates?

For example, in this code

<?php
function eb_mine_views_query_alter(&$view, &$query) {
  if ($view->name == 'statuser') {
    dsm($query, 'before');
    $query->where[0]['type'] = 'OR';
    dsm($query, 'after');
  }
}
?> 

Will change

SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created
FROM 
{node} node
INNER JOIN {taxonomy_index} taxonomy_index_value_0 ON node.nid = taxonomy_index_value_0.nid AND taxonomy_index_value_0.tid = :views_join_condition_0
INNER JOIN {taxonomy_index} taxonomy_index_value_1 ON node.nid = taxonomy_index_value_1.nid AND taxonomy_index_value_1.tid = :views_join_condition_1
WHERE ((( (taxonomy_index_value_0.tid = :db_condition_placeholder_2) )**AND**( (taxonomy_index_value_1.tid = :db_condition_placeholder_3) )))
ORDER BY node_created DESC

to

SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created
FROM node node
OUTER JOIN taxonomy_index taxonomy_index_value_0 ON node.nid = taxonomy_index_value_0.nid AND taxonomy_index_value_0.tid = '9'
OUTER JOIN taxonomy_index taxonomy_index_value_1 ON node.nid = taxonomy_index_value_1.nid AND taxonomy_index_value_1.tid = '6'
WHERE ((( (taxonomy_index_value_0.tid = '9') )OR( (taxonomy_index_value_1.tid = '6') )))       
ORDER BY node_created DESC LIMIT 5 OFFSET 0;

As you can see the query changed from AND to OR through the use of OUTER JOINs.

Now I want to change the same code like this:

SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created
FROM node node
LEFT OUTER JOIN taxonomy_index taxonomy_index_value_0 ON node.nid = taxonomy_index_value_0.nid AND taxonomy_index_value_0.tid = '9'
LEFT OUTER JOIN taxonomy_index taxonomy_index_value_1 ON node.nid = taxonomy_index_value_1.nid AND taxonomy_index_value_1.tid = '6'
WHERE ((( (taxonomy_index_value_0.tid = '9') )OR( (taxonomy_index_value_1.tid = '6') ))) 
ORDER BY node_created DESC LIMIT 5 OFFSET 0;

rather than using OUTER JOIN I want to use LEFT OUTER JOIN.

So how do I do this in my hook?

1 Answer 1

0

You can use query alter will help you out

function custom_views_query_alter(&$view, &$query) {
    if($view->name == "tag_cloud"){
        print_r($query);// before modifying the query.
        $query->table_queue['taxonomy_index']['join']->type = "LEFT OUTER JOIN";
        //sample field change where  ever you want 
        print_r($query);// after modifying the query .
    }
}

hope this helps

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.