Drupal views subqueries

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');
}

4 thoughts on “Drupal views subqueries

  1. Litza

    I’m glad I came across your post; I’m trying to do exactly this, and had hit on basically this approach. But when I execute the query, the subquery is rewritten to force it into a table name (i.e., spaces and punctuation removed and wrapped in curly brackets).

    How did you prevent this from happening in your case? I’m using hook_views_query_alter in Drupal 7 and have copied your code, just modifying the individual pieces to reflect my query.

    Reply
    1. rukaya Post author

      Well I did this in Drupal 6 so I’m quite surprised it’s actually worked at all with D7 and views 3. So what error do you get from it rewriting the subquery so the table name is wrapped in curly braces etc? Have you tried adding it in the format it wants it in beforehand, so in my code that would be:

      $subquery = ‘(SELECT max(nr.vid) as vid FROM {node_revisions} nr GROUP BY nr.nid )’

      I don’t know if that would make it behave better.You could also try using hook_views_pre_execute which is called when the query is fully built, just before it runs through the db_rewrite_sql function, and then doing some regex replacement stuff to add whatever it is you need. Nasty way of doing it but if you’re desperate…

      Reply
      1. Jeroen

        Hello,

        I’m facing the same problem now and I know it’s been a long time since you commented here, but I was wondering if you ever found a solution?

        Reply
        1. Grigoriy Shlyapkin

          Hello,

          I know that it’s been a long time since your last comment, but for D7 this code snippet works for me (which I’ve used in query method of views handler):

          query;
          // Define subquery.
          $subquery = db_select('pm_index', 'pmi')
          ->fields('pmi', ['thread_id']);
          $subquery->addExpression('MAX(mid)', 'mid');
          $subquery->groupBy('pmi.thread_id');
          // Join son subquery.
          $join = new views_join('$subquery_table_formula', 'node', 'nid', 'nid');
          $join->extra_type = 'AND';
          $join->table = $subquery;
          $join->left_field = 'pm_index.thread_id';
          $join->field = 'thread_id';
          $join->type = 'INNER';
          $query->add_relationship('pm_mid', $join, $subquery);
          $query->add_field('pm_mid', 'mid', 'max_mid');

          Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>