2

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?

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.