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?