Using groupby in hook_views_query_alter()

Question

I have a view that is using a format that doesn't include group by fields in its settings. I'm trying to mimic that by using the hook_views_query_alter() function in a module.

I know the code is running, because I see the DPM value and the error log string, but the group by doesn't seem to be working. I know that group by in views is a different thing than mySQL's group_concat (which you can use via aggregation, but that doesn't work in this case because we need one field to not be aggregated.) So I'm not expecting to see a group_concat in the printed-out query. But I would have expected it to produce the same results as if I had set up Group By in unformatted list settings for the same four fields (which does work as expected.)

Here is what my view looks like without any group-by (either in the settings or the module):

You can see that there are two complete chunks of information for April Carlisle, because she is speaking at two sessions.

If I turn on Group By for speaker name (content: title), job title, and company in unformatted list settings, this is what I get (and what I want):

So I had thought that my module code would reproduce that result, but it's not. Can anyone tell me why?

Here's the main function:

function group_speaker_carousel_views_query_alter(&$view, &$query) {
  switch($view->name) {
    case 'speakers_carousel':  
      error_log("view name now: ".$view->name);
      $query->groupby[0]['field'] = "title";
      $query->groupby[1]['field'] = "field_job_title";
      $query->groupby[2]['field'] = "field_company";

      $select_query = $query->query();
      $query_string = (string)$select_query;
      dpm($query_string);
      break;
  }
}

Answer 1

"Group By" is not working on hook_views_query_alter and it is views module issue. that is reported at https://drupal.org/node/590534, http://drupal.org/node/1608742, http://drupal.org/node/389230#comment-2637200

So, You should use other hooks such as general hook_query_alter.

function hook_query_alter( &$query) {

  if($query->alterMetaData['view']->name == "your_views_name"){
     $query->groupBy("domain_source.domain_id");
     //also you can unset other group by critera by using, unset($query->getGroupBy());
  }
}

Answer 2

You may use groupby function in hook_views_query_alter() function like:

//$query->add_field('table_alias', 'field_name', 'field_alias', array('function' => 'groupby'));
$query->add_field('node', 'nid', 'node_nid', array('function' => 'groupby'));
//Distinct = TRUE -used to avoid unnecessary groupby conditions
$query->distinct=TRUE;

Answer 3

The views distinct module will be the best solution to remove duplicates from the view.

INTRODUCTION:

Relationships or other joins in Views often create "duplicate" results. For example, a node with a field that has multiple values may show up in the View once per value in the multi-value field. It's frustrating, and the "DISTINCT" SQL query option in the Views UI does not actually solve the problem because the result row is technically distinct. This module aims to give a simple GUI method to remove or aggregate these "duplicate" rows.

For any given field, including "Global: Text" fields, you can optionally mark the field as filtered ("Filter Repeats") or aggregated ("Aggregate Repeats"). All rows with the same value in that field will either be removed as duplicates (filtered), or aggregated in-line.

The "value" of the field as used for filtering or aggregation can be taken pre-render (fastest and totally cacheable), or post-render (after any rewrite rules or other transformations have occurred). Post-render actions are a bit slower (the View must be re-rendered, though the query is not re-run), but also work with Global fields, like Global: Text w/rewrite rules.