Pager Gone when I edit query in "views_pre_execute"

Question

Today I faced a strange problem.

Indeed I tried to manipulate SQL QUERY of a view in "views_pre_execute" event.

I made a module with name "modify_view_query" and created related function in my module.

function modify_view_query_views_pre_execute(&$view) { 
}

the view name is "view_products" so I added this condition to the function.

function modify_view_query_views_pre_execute(&$view) { 
    if($view->name=="view_products") {

    }
}

Then I retrieved the SQL query and tried to inject.

function modify_view_query_views_pre_execute(&$view) { 
    if($view->name=="view_products") {
        $sqlStr = $view->build_info['query'];
    }
} 

The original Query is

SELECT DISTINCT(node.nid) AS nid, node.type AS node_type, node.vid AS node_vid, 
    node.title AS node_title, node.language AS node_language, 
    node_data_field_product_brand.field_product_brand_value 
        AS node_data_field_product_brand_field_product_brand_value, 
    node_data_field_product_brand.field_product_dosage_value 
        AS node_data_field_product_brand_field_product_dosage_value, 
    node_data_field_product_brand.field_product_package_value 
        AS node_data_field_product_brand_field_product_package_value, 
    node_data_field_product_brand.field_product_drug_category_value 
        AS node_data_field_product_brand_field_product_drug_category_value, 
    node_data_field_product_brand.field_product_indication_value 
        AS node_data_field_product_brand_field_product_indication_value, 
    node_data_field_product_brand.field_product_type_value 
        AS node_data_field_product_brand_field_product_type_value, 
    node_data_field_product_brand.field_product_patient_advisor_nid 
        AS node_data_field_product_brand_field_product_patient_advisor_nid 

    FROM {node} node LEFT JOIN {content_type_product} node_data_field_product_brand 
    ON node.vid = node_data_field_product_brand.vid 

    WHERE 
        (node.status = 1) 
        AND (node.type in ('%s')) 
        AND (node.language in ('%s')) 
        AND (SUBSTR(node.title, 1, 1) = '%s') 
        GROUP BY nid ORDER BY node_title ASC

I want to inject an OR condition to my code. indeed I want to replace SUBSTR(node.title, 1, 1) = '%s' with SUBSTR(node.title, 1, 1) = '%s' OR SUBSTR(node.title, 1, 1) = '%s'

So I used string replacement function as follow.

$sqlStr = str_replace("SUBSTR(node.title, 1, 1) = '%s'","SUBSTR(node.title, 1, 1) = '%s' OR SUBSTR(node.title, 1, 1) = '%s'",$sqlStr);

Finally I set the parameter and new SQL string for the view.

    $view->build_info['query_args'][2] = 'A';
    $view->build_info['query_args'][3] = 'B';
    $view->build_info['query'] = $sqlStr;

The Final Function is

function modify_view_query_views_pre_execute(&$view) {
   if($view->name=="view_products") {
    $sqlStr = $view->build_info['query'];
    if($view->build_info['query_args'][2] == "A" || $view->build_info['query_args'][2] == "B")
    {
        $sqlStr = str_replace("SUBSTR(node.title, 1, 1) = '%s'","SUBSTR(node.title, 1, 1) = '%s' OR SUBSTR(node.title, 1, 1) = '%s'",$sqlStr);
        $view->build_info['query_args'][2] = 'A';
        $view->build_info['query_args'][3] = 'B';
        $view->build_info['query'] = $sqlStr;
    }
   }
}

every thing works fine. But the problem is when I use this code to manipulate the query the PAGER is disappeared. I am sure that this code causes the problem because when I disable the module or comment the function Pager appears but again when I active the module or un-comment the codes Pager disappears !!!

Please help me. What is my mistake ??

Answer 1

I found the solution of the problem.

Indeed I manipulate the code in wrong function.

I switched to hook_views_query_alter and modify my codes in that function.

now everything works fine with no problem.

my function is

function modify_view_query_views_query_alter(&$view, &$query){ }

and I modify the query parts by changing the array values.

$query->where[0]["clauses"] and $query->where[0]["args"]