Forcing SQL groupby in views 3 with query comments.

Public

This is a trick I found that allows you to use query comments to force the use of the SQL "GROUP BY" clause in the view query. Works really well.

Get raw version
php
  1. /**
  2.  * Found this trick on theoleschool.com.
  3.  *
  4.  * Description: Allows the view developer to specify the query
  5.  * group by action in the query comments. Great way to force the
  6.  * removal of duplicates.
  7.  *
  8.  * Just go into your query comments and type in "groupby:" with
  9.  * that colon followed by the field you want to group by.
  10.  *
  11.  * Examples...
  12.  * groupby:node.nid
  13.  * groupby:file_managed_file_usage.fid
  14.  *
  15.  * Ref-Comment: http://theoleschool.com/comment/496#comment-496
  16.  * Ref-Article: http://theoleschool.com/blog/using-hookviewsalter-add-group-statement
  17.  */
  18. function mymodule_query_alter(QueryAlterableInterface $query) {
  19. if ($query->hasTag('views')) {
  20. static $count;
  21. $view =& $query->getMetaData('view');
  22.  
  23. if (strstr($groupby = $view->query->options['query_comment'], 'groupby')) {
  24. list($action, $field) = explode(':', $groupby);
  25.  
  26. if (strlen($field) > 0) {
  27. $query->groupBy($field);
  28. }
  29. }
  30. }
  31. }

Comments

Gil's picture

There is actually a small bug in the module: Strict warning: Only variables should be passed by reference. I fixed it by replacing:
$view =& $query->getMetaData('view');
to
$viewGroup = $query->getMetaData('view');
$view =& $viewGroup;

Thank you

alan cooper's picture

in which this code i put

kalabro's picture

Views 3 supports aggregation out of the box. In which cases this technique is useful?