For each field which contains aggregation type, ex. COUNT, groupby key is added to mysql. However some times either this field doesn't have a corresponding database column (ex. generated by views calc) or this field is just not the group key, we might want to remove this field name from the group by list. Is there a way to do it ? thanks.

Comments

dawehner’s picture

In theory all handlers can specify "usesGroupBy" and then get excluded based on that properties,
maybe the fields that use views calc(don't know much about that) could do that.

windmaomao’s picture

ok, thanks dawehner, that's interesting. I don't know you can exclude groupby.

this groupby is added by Views, i tracked it down to the following code in views_plugin_query_default.inc, I comment out one of the line which includes groupby for formula, i don't know if this will cause problem on other views yet. At least it solves the problem for now.

I wonder why groupby needs to be added for formula or non-aggregation fields ? puzzled.

      if (!empty($field['function'])) {
        $info = $this->get_aggregation_info();
        if (!empty($info[$field['function']]['method']) && function_exists($info[$field['function']]['method'])) {
          $string = $info[$field['function']]['method']($field['function'], $string);
          $placeholders = !empty($field['placeholders']) ? $field['placeholders'] : array();
          $query->addExpression($string, $fieldname, $placeholders);
        }

        $this->has_aggregate = TRUE;
      }
      // This is a formula, using no tables.
      elseif (empty($field['table'])) {
        // $non_aggregates[] = $fieldname;   <-- COMMENT OUT
        $placeholders = !empty($field['placeholders']) ? $field['placeholders'] : array();
        $query->addExpression($string, $fieldname, $placeholders);
      }

merlinofchaos’s picture

Status: Active » Fixed

In the handler for your field, add this code:

function use_group_by() {
  return FALSE;
}

Then that field will not be given aggregation functions at all.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

mpotter’s picture

Status: Closed (fixed) » Active

Sorry, I'm going to re-open this.

This feature request is still valid for other fields. For example, I have a complex aggregation query where I want to have certain fields available in the query, but I do *not* want them included in the GroupBy. But these are standard fields (such as Title) so adding use_group_by is not a valid solution. Isn't there a way to add an "Ignore" option to the list of Aggregation options?

Here is an example query I am trying to reproduce in Views:

SELECT node.title AS node_title, node.nid AS nid, field_data_oa_parent.oa_parent_target_id, COUNT(field_data_oa_parent.oa_parent_target_id) AS field_data_oa_parent_oa_parent_target_id
FROM 
node node
LEFT JOIN field_data_oa_parent field_data_oa_parent ON node.nid = field_data_oa_parent.entity_id AND (field_data_oa_parent.entity_type = 'node' AND field_data_oa_parent.deleted = '0')
GROUP BY field_data_oa_parent.oa_parent_target_id;

In the above query, adding Title to the GROUP BY messes up the intended query results, but I still need it in the list of fields to return.

mpotter’s picture

Status: Active » Fixed

Hmm, nevermind. I just found this: http://drupal.org/node/1089694#comment-4357936 which explains that each field in the SELECT must either be in the GROUP BY or in an aggregation according to SQL standards and that MySQL is just violating this. I'll try putting something like MIN() around the Title and Nid fields to see if I can get that to work.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

rlmumford’s picture

Issue summary: View changes
Status: Closed (fixed) » Active

I'm going to reopen this because I still can't see anyway to remove a field completely from the aggregation stuff. I have written a variety of views field handlers containing complex logic including group functions. For example:

SUM((cost.num_days) * cost.cost_pd) / COUNT(DISTINCT fees_alias.retained)

and

IF(COUNT(DISTINCT fees_alias.retained) > 0, (SUM(fees_alias.fee) - SUM((cost.num_days) * cost.cost_pd) / COUNT(DISTINCT fees_alias.retained)), - SUM((cost.num_days) * cost.cost_pd))

I don't want any additional grouping functions to a apply to these as they already contain SUMs but they can't be grouped by because they contain functions like SUM.

mcmacerson’s picture

@rlmumford thank you for re-opening.
@mpotter with MySQL your solution works to

try putting something like MIN() around the Title and Nid fields

I probably don't understand enough about it but I find it strange that using the most popular database for Drupal leaves us needing to go thru each Aggregation setting and set it to some arbitrary value such as Minimum just to get the GROUP BY to work properly. Something as simple as a NONE option, set by default so we don't have to go thru each field item one by one to turn off "Group results together" would be nice. At least it's working now but having to set each field's Aggregation setting to an arbitrary value seems like a kludge.

damir.gadiev’s picture

Hello!

I've had similar issue. Any formula, created in add_field would add this field to grouping, if any other field uses grouping.
I solved it in the next way.
1. First, in hook_views_query_alter() added my required field( existing field may be used as well)

$this->query->add_field(NULL, '', '', array('function' => 'CUSTOM_AGGREGATION_PLUGIN'));

2. Then, define 'query_aggregate' plugin, name it CUSTOM_AGGREGATION_PLUGIN and do Your logic there. Field will not be added to group by section of query.

Hamulus’s picture

damir.gadiev, could you please explain point 2 more detailed: how and where you defined query_aggregate