Using hook_views_alter to Add a GROUP BY Statement
I should note this post deals with Views 2.x on Drupal 6.x
When Views doesn't quite spit out the query we are looking for we can turn to hook_views_query_alter(&$view, &$query) to nudge it in the right direction. This is by no means news, and there are many a blog post detailing various ways to alter the query using this function. However, recently, I ran into an odd issue when attempting to add a GROUP BY statement.
The issue can be observed when attempting to use the add_groupby function to a field in the query. As an example, say we have a node view with a few fields (node id, node title, and user id). This will give a simple query:
node.title AS node_title,
users.uid AS users_uid
FROM node node
INNER JOIN users users ON node.uid = users.uid
Now, say we want to do something silly and get this query to group by user id. A node view does not allow for this, so we turn to hook_views_query_alter.
switch($view->name) {
case 'my_view_name':
//add the group by on the user id field
$query->add_groupby('users_uid');
break;
}
}
We expect to see a nice GROUP BY users_uid tacked on to the end of our query, but instead there is a disturbing GROUP BY nid, node_title, users_uid. What gives? Merlinofchaos clarifies for us:
"When using GROUP BY items that appear in the SELECT must either use aggregate functions OR appear in the GROUP BY. This is enforced by postgres, therefore Views must enforce this."
It is good that Views is looking out for the rules of postgres; it's what helps make it the powerful and flexible tool it is. In this application though, I am not worried about postgres and simply want to alter a single Views query on an sql database. I know that I can pass an 'aggregate' flag to the fields in the query object to denote that they use an aggregate function, even if they do not. Worth a try...
switch($view->name) {
case 'dsic_judge_incomplete':
//loop through the fields and add the aggregate indicator
foreach($query->fields as $key => $field) {
$query->fields[$key]['aggregate'] = TRUE;
}
$query->add_groupby('users_uid');
break;
}
}
Turns out this works! Jumping back to our view we see the query we are after:
node.title AS node_title,
users.uid AS users_uid
FROM node node
INNER JOIN users users ON node.uid = users.uid
GROUP BY users_uid
If it looks like a hack, and quacks like a hack, then it's probably a... well ya know. However, I don't have big qualms with implementing this. Views behavior is in place to accommodate a database that, in this instance, is not being used. Further, I am already using a hook function to modify a view beyond what that view is capable of spitting out on its own. Lastly, I am able to accomplish this without modifying Views core. All of that adds up to a valid use case and helps me to sleep at night.