I was using a views_handler_filter in order to add a subquery to a where clause, like so:
/* IN solution */
SELECT DISTINCT(node_revisions.vid) AS vid,
node.nid AS node_nid,
node.type AS node_type,
node.status AS node_status,
FROM node_revisions node_revisions
LEFT JOIN node node ON node_revisions.nid = node.nid
WHERE
node_revisions.vid in
(SELECT MAX(node_revisions.vid) FROM node_revisions GROUP BY node_revisions.nid)
But this is terribly slow. It’s got something to do with using ‘in’. I couldn’t work out how to successfully rewrite the query using ‘exists’, so I rewrote it as a left join like this:
/* LEFT JOIN solution */
SELECT DISTINCT(node_revisions.vid) AS vid,
node.nid AS node_nid,
node.type AS node_type,
node.status AS node_status,
FROM node_revisions node_revisions
LEFT JOIN ( SELECT max(nr.vid) as vid FROM node_revisions nr GROUP BY nr.nid ) nr ON nr.vid = node_revisions.vid
LEFT JOIN node node ON node_revisions.nid = node.nid
WHERE
nr.vid is not null
With the SQL fixed and running smoothly (I am still not sure why using ‘IN’ was such a big issue for mysql), I needed to edit the query somehow, and I wasn’t going to bother doing it with filter handlers. I could have used (http://drupal.org/node/1065554) hook_views_pre_execute(&$view), but I decided to use hook_views_query_alter(), like this:
/**
* Implementation of hook_views_query_alter().
*/
function workflow_views_query_alter(&$view, &$query) {
if($view->name != 'my_content')
return;
$subquery = '(SELECT max(nr.vid) as vid FROM node_revisions nr GROUP BY nr.nid )';
$join = new views_join('node_revisions', 'node_revisions', 'vid', 'vid');
$join->definition = array('table' => $subquery, 'left_field' => 'vid', 'field' => 'vid', 'left_table' => 'node_revisions');
$join->extra_type = 'AND';
$join->table = $subquery;
$join->left_field = 'node_revisions.vid';
$join->field = 'vid';
$join->type = 'LEFT';
$join->adjusted = true;
$query->table_queue['nr'] = array(
'table' => $subquery,
'alias' => 'nr',
'num' => 1,
'join' => $join,
'relationship' => 'node_revisions');
$query->where[2] = array( // Note: do print_r($query) here to find out where to place your where
'clauses' => array('nr.vid is not null'),
'args' => array(),
'type' => 'AND');
}