Better WHERE's for your Drupal forums with hook_views_query_alter

Sometimes, Drupal development could benefit from those monkeys with the typewriters who are busily (eventually) reproducing Shakespear's works. Fortunately, I am just about 10% more efficient than one of those monkeys. Faced with a View that couldn't quite do what I wanted, I did a bit of floundering in the Views 2 API and intervened via code.

I had a view with a few different displays, each of them with slightly overridden filters, like this one used in a Panels pane:

Node: Published Yes
Node: Type in Forum topic, [Content Type A], [Content Type B]
Node: Title exposed

The problem was that I needed to add something just a bit more complex than Views could help me with via the UI. I needed to add a two-part search argument / filter that grabbed all forums whether or not they had comments and the other content types only if they *did* have comments. [It's for a "conversation aggregator" that's getting released today.] So how do you add this pseudocode via the UI?

...AND (node.type='Forum' OR node.comment_count >=1)

Short answer: You don't. It seems silly, I know, but you need to alter the query to add even this minor complexity. The following would go into YOURMODULE.views.inc (or includes/YOURMODULE.views.inc) in your existing custom module, which is hopefully called something better than YOURMODULE. Where YOURVIEW is, just add the name of your view. [In my case, I didn't want to use the stock Advanced Forums view, but a heavily modified version that did few things differently.] It's also worth noting the first parameter in the add_where() function, in this case advf_filter. If your clause name already exist, it will be created.

<?php
function YOURMODULE_views_query_alter(&$view, &$query) {
  if ($view->name == 'YOURVIEW') {
    $view->query->add_where('advf_filter',"(node.type='%s' OR node_comment_statistics.comment_count >= %d)",'forum',1);
    $view->query->set_group_operator('AND');
  }
}
?>

That's just the beginning of what you can do. Also in my future is probably some GROUP BY action that will make inside-Drupal reporting possible.

Using this hook, query caching will still work - an added performance bonus. Enjoy.

DV77WP3EKY2Q