How to modify drupal views query

Question

So Drupal views is creating a query and I need to change the where clause conditions and move it to the on clause. This seems to give me the results that I am looking for. I can not figure out how to do this with views in its self so I have to alter the views query in order to do this.

This is the query that drupal is creating.

SELECT field_data_field_procedure_date_time.delta AS     field_data_field_procedure_date_time_delta, field_data_field_procedure_date_time.entity_id     AS date_id_field_procedure_date_time, field_data_field_procedure_date_time.delta AS     date_delta_field_procedure_date_time, node.title AS node_title, node.nid AS nid, field_data_field_procedure_date_time.language AS field_data_field_procedure_date_time_language, field_data_field_procedure_date_time.bundle AS field_data_field_procedure_date_time_bundle, field_data_field_procedure_date_time.field_procedure_date_time_value AS field_data_field_procedure_date_time_field_procedure_date_ti, field_data_field_procedure_date_time.field_procedure_date_time_rrule AS field_data_field_procedure_date_time_field_procedure_date_ti_1, 'node' AS field_data_field_procedure_date_time_node_entity_type
FROM 
{node} node
LEFT JOIN {field_data_field_procedures} field_data_field_procedures ON node.nid =     field_data_field_procedures.field_procedures_target_id AND     (field_data_field_procedures.entity_type = 'user' AND field_data_field_procedures.deleted = '0')
LEFT JOIN {users} field_procedures_node ON field_data_field_procedures.entity_id = field_procedures_node.uid
LEFT JOIN {field_data_field_pre_op_procedure} field_data_field_pre_op_procedure ON node.nid = field_data_field_pre_op_procedure.field_pre_op_procedure_target_id AND (field_data_field_pre_op_procedure.entity_type = 'user' AND field_data_field_pre_op_procedure.deleted = '0')
LEFT JOIN {users} field_pre_op_procedure_node ON field_data_field_pre_op_procedure.entity_id = field_pre_op_procedure_node.uid
LEFT JOIN {field_data_field_procedure_date_time} field_data_field_procedure_date_time ON node.nid = field_data_field_procedure_date_time.entity_id AND (field_data_field_procedure_date_time.entity_type = 'node' AND field_data_field_procedure_date_time.deleted = '0')
WHERE (( (DATE_FORMAT(CONVERT_TZ(field_data_field_procedure_date_time.field_procedure_date_time_value, 'UTC', 'America/Boise'), '%Y-%m') >= '2014-10' AND DATE_FORMAT(CONVERT_TZ(field_data_field_procedure_date_time.field_procedure_date_time_value, 'UTC', 'America/Boise'), '%Y-%m') <= '2014-10') )AND( (field_procedures_node.uid = '3' ) AND (field_pre_op_procedure_node.uid = '3' ) )AND(( (node.status = '1') AND (node.type IN  ('procedures')) )))

and I need to get it formated to look like this.

SELECT DISTINCT 
field_data_field_procedure_date_time.delta AS 
field_data_field_procedure_date_time_delta, field_data_field_procedure_date_time.entity_id AS 
date_id_field_procedure_date_time, field_data_field_procedure_date_time.delta AS 
date_delta_field_procedure_date_time, node.title AS 
node_title, node.nid AS 
nid, field_data_field_procedure_date_time.language AS 
field_data_field_procedure_date_time_language,   field_data_field_procedure_date_time.bundle AS 
field_data_field_procedure_date_time_bundle,    field_data_field_procedure_date_time.field_procedure_date_time_value AS 
field_data_field_procedure_date_time_field_procedure_date_ti,   field_data_field_procedure_date_time.field_procedure_date_time_rrule AS 
field_data_field_procedure_date_time_field_procedure_date_ti_1, 'node' AS 
field_data_field_procedure_date_time_node_entity_type
FROM 
node node left JOIN 
field_data_field_procedures field_data_field_procedures ON node.nid =   field_data_field_procedures.field_procedures_target_id AND   (field_data_field_procedures.entity_type = 'user' AND field_data_field_procedures.deleted  = '0')  LEFT JOIN 
users field_procedures_node ON field_data_field_procedures.entity_id =   field_procedures_node.uid AND ( field_procedures_node.uid = '53' ) LEFT JOIN 

field_data_field_pre_op_procedure field_data_field_pre_op_procedure ON node.nid =   field_data_field_pre_op_procedure.field_pre_op_procedure_target_id AND  (field_data_field_pre_op_procedure.entity_type = 'user' AND   field_data_field_pre_op_procedure.deleted = '0') LEFT JOIN 
users field_pre_op_procedure_node ON field_data_field_pre_op_procedure.entity_id =  field_pre_op_procedure_node.uid AND (field_pre_op_procedure_node.uid = '53' )  LEFT JOIN 


field_data_field_procedure_date_time field_data_field_procedure_date_time ON node.nid = field_data_field_procedure_date_time.entity_id AND  (field_data_field_procedure_date_time.entity_type = 'node' AND  field_data_field_procedure_date_time.deleted = '0')
WHERE 


(( (node.status = '1') AND (node.type IN  ('pre_op', 'procedures')) ))

Its basically the same query except I moved the where conditions of the uid=53 into the ON clause instead of the where clause. Anyone know how I can do this with either changing some settings in the views or by manually modifiying the query with a hook_views_query_alter or a hook_views_pre_exectute?

Answer 1

I will show you a sample of how to do it...

In this case if you want to alter the nid to be passed as query to views via filter

function hook_views_query_alter(&$view, &$query){
    switch($view -> name){
        case 'view_name':
            if($view->current_display == 'block'){
                $nids = calculate_nid();
                $nids = !empty($nids) ? $nids : array(0);
                $query->where[1]['conditions'][2]['value'] = $nids;
                $query->where[1]['conditions'][2]['operator'] = 'IN';
            }
            break;
}


function calculate_nid() {
// calculations to get nid. 
// here you can write sql queries
}