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.