Views query alter but Views calc is not rendering right result

Question

I am alter the views query with this

<?php
function cc_invoice_views_query_alter(&$view, &$query) {
    foreach ( $query->where[0]['clauses'] as $key => $value) {
      $explode = explode('.',$value);
      if($explode[1] == "field_status_value = '%s'") {
         $alias = $explode[0];
         if($query->where[0]['args'][$key] == '0') {
            $query->where[0]['clauses'][$key] =  $alias . ".field_status_value IN ('%s' ,'%s')";
            #$query->where[0]['clauses'][$count] =  $alias . ".field_status_value IN '%s'";
            $query->where[0]['args'][] = -2;
         }
         elseif ($query->where[0]['args'][$key] == '1') {
            $query->where[0]['clauses'][$key] =  $alias . ".field_status_value IN ('%s' ,'%s')";
            $query->where[0]['args'][] = 8;
       }
         break;
      }
    }
}

?>

In this code, I just altered the views query. the result is displaying rightly, but in the views calculation section, the subtotal and total is not rendering right. It does not add the altered value while calculating the total or sub-total. Is there anything else i need to correct here. Please advise.

Pawel G · Answer

I have the same problem (pagers not working for custom queries), and while I am getting close to the solution I do not have it yet. Something that helped me to understand what is happening is a piece of code from OG that generates a table with pagers.

og_ui.admin.inc line 221

query:

$query = db_select('users', 'u');
  $query->innerJoin('og_membership', 'ogm', 'u.uid = ogm.etid');
  $query
    ->condition('ogm.gid', $group->gid, '=')
    ->condition('ogm.entity_type', 'user', '=');

and the important part where the query gets cloned jost for the counter:

$count_query = clone $query;
  $count_query->addExpression('COUNT(u.uid)');

$query = $query->extend('PagerDefault')->extend('TableSort');
  $query
    ->fields('u', array('uid', 'name'))
    ->fields('ogm', array('state', 'created'))
    ->limit(25)
    ->orderByHeader($header)
    ->setCountQuery($count_query);
  $result = $query->execute();

My current solution is 
do not use pagers when you edit view queries, 
if you have to use a pager than create a whole template using the example from OG module (og_ui_menu() in og_ui.module + og_ui.admin.inc).

Hope it helps, but I would still be happy to know how to fix counter in views.

Views query alter but Views calc is not rendering right result

One Answer

Add your own answers!

Ask a Question