How to add a correlated subquery expression in hook_views_query_alter?

Question

In hook_views_query_alter, how to add a condition which is a correlated subquery in which the value of the condition is an expression?

Given a view the produces this query:

SELECT node.nid, node.title, history.state  
FROM node n 
LEFT JOIN node_revision nr ON n.vid = nr.vid 
INNER JOIN history h ON nr.vid = h.vid 
WHERE (( (node.type IN ('blog')) ))

In order to show only the latest [state] for a given version (vid) of a node, we need to add this condition:

AND h.hid = (select max(hid) from history h2 
where h2.nid = n.nid 
and h2.vid = n.vid group by h.nid, h.vid)

So here's the statement we'd like to end up with after hook_views_query_alter:

SELECT node.nid, node.title, history.state  
FROM node n 
LEFT JOIN node_revision nr ON n.vid = nr.vid 
INNER JOIN history h ON nr.vid = h.vid 
WHERE (( (node.type IN ('blog')) ))
AND h.hid = (select max(hid) from history h2 
where h2.nid = n.nid 
and h2.vid = n.vid 
group by h.nid, h.vid)

So we can see that our modified sql works, so here's our attempt (not working) to modify the statement on the fly:

function mymodule_views_query_alter($view, $query) {
  if ($view->name == 'my_view') {
  $expression = '(select max(hid) from 
   workbench_moderation_node_history h 
   where h.nid = node.nid 
   and h.vid = node.vid 
   group by h.nid, h.vid)';

  $condition = array();
  $condition['field'] = 'history.hid';
  $condition['value'][] = $expression;
$condition['operator'] = '=';    
$query->where[1]['conditions'][] = $condition;
  }
}

This doesn't work (with or without the parenthesis) because the condition is an expression, not a value. To validate that the syntax of adding a condition is correct, this will at least return results:

function mymodule_views_query_alter($view, $query) {
  if ($view->name == 'my_view') {
    $condition = array();
    $condition['field'] = 'history.hid';
    $condition['value'][] = '1';
    $condition['operator'] = '>';    
    $query->where[1]['conditions'][] = $condition;
  }
}

How might we go about coding this constraint?

Answer 1

This doesn't answer the question as stated, but provides a very simple approach to get at the result using Views without implementing hook_views_query_alter.

The example provided is for workbench_moderation, although the principle applies in other scenarios as well:

Creating a new Workbench view showing recently updated content and its state