1

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?

2 Answers 2

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

Maybe this is too obvious, but have you looked at hook_views_query_alter?

There is a more general Drupal query alter hook, but if it's coming from views, it'll be more efficient to intercept it with the more specific hook.

2
  • Yes I have looked at that. However, I can not figure out how in the world to take the conditions from the where clause and use it in the on clause.. Do you know how I can do that?
    – Atrium
    Commented Oct 29, 2014 at 21:29
  • api.drupal.org/api/drupal/…
    – Andreas
    Commented Mar 28, 2023 at 10:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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