2

I have a view with multiple fields and the fields have multiple values, so I get many duplicates in results. When I try to use distinct, nothing happens, because select distinct does not affect on multiple values. I have predictable, but non desired result.

When aggregation is disabled I don't have any "groupby" directives in resulting SQL.

When I enable aggregation, I see in resulting SQL the following:

GROUP BY field_data_field_tour_short_name_node_entity_type, 
field_data_field_tour_short_name_field_tour_short_name_value, node_title, 
nid_1, field_data_field_arr_dates_node_entity_type, 
field_data_field_arr_dates_field_arr_dates_value, 
field_data_field_tour_duration_node_entity_type, 
field_data_field_tour_duration_field_tour_duration_tid, 
field_data_field_tour_minimal_price_node_entity_type, 
field_data_field_tour_minimal_price_field_tour_minimal_price

So, as you can see, I have ALL the fields in my view.

When I disable aggregation and try to use hook_views_query alter

$query->add_field('node', 'nid', 'nid', array('function' => 'groupby'));
$query->add_groupby('node.nid');

I get the following:

GROUP BY node.nid, field_data_field_tour_short_name_node_entity_type, 
field_data_field_arr_dates_node_entity_type, 
field_data_field_tour_duration_node_entity_type, 
field_data_field_tour_minimal_price_node_entity_type

As you can see, I get less fields, but not one.

But I need simply:

GROUP BY node.nid

How can I get grouping by only on field instead of many?

UPDATE: I found the following strings in views_plugin_query_default.inc

$groupby = array_unique(array_merge($this->groupby, $non_aggregates));
  foreach ($groupby as $field) {
    $query->groupBy($field);
  }

So views for some reason add other fields to groupby if groupby is present. How to avoid this adding? I could change the file, but this is not proper way.

  • You are using aggregation incorrectly. To collapse seemingly similar rows into 1 row you need to group on a common field value unique to that group and set it to group on COUNT (or SUM/MIN/MAX) and then hide that from display. For the rest of the fields you just leave the aggregation at default. And remove all sorting at first and only when you are happy with aggregation you add the sorting in. – J. Reynolds Mar 3 '16 at 5:54
4

I had the same problem. Try to implement the group by in hook_views_pre_execute, it's work for me.

function MYMODULE_views_pre_execute(&$view){
  if ($view->name == 'YOUR_VIEWS' &&  $view->current_display == 'SOME_DISPLAY') {   
    $view->build_info['query']->groupBy('node.nid');
  }
}

If you are using a pager, updating $view->build_info['count_query'] with the same changes should keep the pager consistent with the query results.

  • Perfect, but views pager is not correct! – Mohammed Gomma Jan 25 '18 at 9:14
2

enter image description hereYou can unset the fields that you are not interested in. This code works for me, in hook_query_alter:

    if ($query->hasTag('views_' . $view_name)) {
        $fields =& $query->getGroupBy();
        unset($fields['item_id']);
        unset($fields['field_data_field_field_rf_product_node_entity_type']);
        $query->groupBy('field_oem_ubication_field_collection_item__field_data_field_');
        $query->groupBY('taxonomy_term_data_field_data_field_brand_name');
    } // if

As you can see, I add some fields ( $query->groupBy ) and unset others.

  • I need all fields in results but I want to group by only one field. If I unset some fields, they will be not selected – Sergey Kravchenko Mar 2 '16 at 15:56
  • I am not sure, but I think that only the fields inside groub by are affected, not those which are selected. I will try to check it up further. – Cesar Mar 3 '16 at 7:16
  • I am also facing the exact same issue. Any updates? @SergeyKravchenko If you have found a solution, please update here so we can be benefitted. – Supriya Rajgopal Jul 19 '16 at 13:18
  • I think that you can set the "group by" field in the Admin panel. See image in my (edited) reply. If you uncheck, you will not group by the field. There is no need to do unset in the hook. – Cesar Jul 20 '16 at 11:59
1

A screenshot would be really beneficial here as I'm getting it hard to visualise the problem. But generally, hiding the field you want to group by and then grouping by it is the general way to go.

https://www.drupal.org/project/views_merge_rows is also a bit of an alternative to aggregate.

Anyways, just throwing out ideas cause no one else has answered.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.