Custom views query - pager dosn't work I am trying to modify view query, basically I build my custom query and assigned it to: 

$view-&gt;build_info['query'] = $query;
$view-&gt;build_info['count_query'] = $query;


everything has been done in hook_views_pre_execute. I tried to do the same in hook_views_query_alter but I wasn't able to construct more complex where clause.

Everything works fine apart from a pager. I noticed that I get pager but main query is not modified with LIMIT option so when I click on particular number of pager my result has not being updated. Below is a query which I need:

SELECT DISTINCT 
  dat.field_date_and_time_value AS field_date_and_time_value, 
  n.created AS created, 
  n.nid AS nid FROM node n 
LEFT OUTER JOIN og_membership pb 
ON n.nid = pb.etid 
AND pb.entity_type = :entity_type 
LEFT OUTER JOIN field_data_field_date_and_time dat 
ON n.nid = dat.entity_id 
AND (dat.entity_type = :entity_type AND dat.deleted = :deleted) 
LEFT OUTER JOIN field_data_field_og_public_to pt 
ON n.nid = pt.entity_id 
AND (pt.entity_type = :entity_type AND pt.deleted = :deleted) WHERE 
(n.type = :db_condition_placeholder_0) 
AND 
(n.status = :db_condition_placeholder_1) 
AND 
(dat.field_date_and_time_value &gt;= NOW()) 
AND
( 
    (pb.gid = :db_condition_placeholder_2) 
    OR 
    (pt.field_og_public_to_target_id = :db_condition_placeholder_3) 
    OR
    ( 
        (pt.field_og_public_to_target_id = :db_condition_placeholder_4) 
        AND 
        (pb.gid IS NULL ) 
    )
) 
ORDER BY dat.field_date_and_time_value ASC, n.created DESC


I created it like that:

$query = db_select('node', 'n');
$query-&gt;distinct();
$query-&gt;fields('dat', array('field_date_and_time_value'));
$query-&gt;fields('n', array('created', 'nid'));
$query-&gt;leftJoin('og_membership', 'pb', 'n.nid = pb.etid AND pb.entity_type = :entity_type', array(':entity_type' =&gt; 'node'));
$query-&gt;leftJoin('field_data_field_date_and_time', 'dat', 'n.nid = dat.entity_id AND (dat.entity_type = :entity_type AND dat.deleted = :deleted)', array(':entity_type' =&gt; 'node', ':deleted' =&gt; '0'));
$query-&gt;leftJoin('field_data_field_og_public_to', 'pt', 'n.nid = pt.entity_id AND (pt.entity_type = :entity_type AND pt.deleted = :deleted)', array(':entity_type' =&gt; 'node', ':deleted' =&gt; '0'));

$query-&gt;condition('n.type', $view-&gt;argument['type']-&gt;argument);
$query-&gt;condition('n.status', $view-&gt;filter['status']-&gt;value);
$query-&gt;where('dat.field_date_and_time_value &gt;= NOW()');
$subcondition = db_or()
-&gt;condition('pb.gid', $view-&gt;argument['gid']-&gt;argument)
-&gt;condition('pt.field_og_public_to_target_id', $view-&gt;argument['gid']-&gt;argument)
-&gt;condition(db_and()-&gt;condition('pt.field_og_public_to_target_id', $view-&gt;argument['gid']-&gt;argument)-&gt;isNull('pb.gid'));
$query-&gt;condition($subcondition);

$query-&gt;orderBy('dat.field_date_and_time_value', 'ASC');
$query-&gt;orderBy('n.created', 'DESC');


Any idea why pager doesn't work?
Ok, I resolved the issue with the hook_views_query_alter using add_where_expression method. It allows you to create complex where conditions. Below is example

// Delete current where conditions created by view and replace it by custom ones.
unset($query-&gt;where);
$query-&gt;add_where_expression(0,'node.type = :node_type', array(':node_type' =&gt; $node_type));
$query-&gt;add_where_expression(0,'node.status = :node_status', array(':node_status' =&gt; $node_status));
$query-&gt;add_where_expression(0,"(CASE node.type WHEN 'event' THEN field_data_field_date_and_time.field_date_and_time_value WHEN 'training_course' THEN field_data_field_date_and_time.field_date_and_time_value ELSE NOW() END) &gt;= NOW()");

$or_conditions = array();

$or_conditions[] = '(og_membership.gid = :public_by_gid)';
$or_conditions[] = '(field_data_field_og_public_to.field_og_public_to_target_id = :public_to_gid)';
$or_conditions[] = '((field_data_field_og_public_to.field_og_public_to_target_id = :public_to_gid) AND (og_membership.gid IS NULL))';

$or_condition = implode(' OR ', $or_conditions);

$query-&gt;add_where_expression(0, $or_condition, array(':public_by_gid' =&gt; $public_by_gid, ':public_to_gid' =&gt; $public_to_gid));


I hope it was useful for someone.