How to make view to group by only one field?

Question

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.

Answer 1

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.

Answer 2

You 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.

Answer 3

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.