1

I am trying to modify view query, basically I build my custom query and assigned it to:

$view->build_info['query'] = $query;
$view->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 >= 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->distinct();
$query->fields('dat', array('field_date_and_time_value'));
$query->fields('n', array('created', 'nid'));
$query->leftJoin('og_membership', 'pb', 'n.nid = pb.etid AND pb.entity_type = :entity_type', array(':entity_type' => 'node'));
$query->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' => 'node', ':deleted' => '0'));
$query->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' => 'node', ':deleted' => '0'));

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

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

Any idea why pager doesn't work?

1
  • nobody with the similar problem?
    – user7929
    Commented Mar 31, 2015 at 18:02

1 Answer 1

1

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->where);
$query->add_where_expression(0,'node.type = :node_type', array(':node_type' => $node_type));
$query->add_where_expression(0,'node.status = :node_status', array(':node_status' => $node_status));
$query->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) >= 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->add_where_expression(0, $or_condition, array(':public_by_gid' => $public_by_gid, ':public_to_gid' => $public_to_gid));

I hope it was useful for someone.

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.