Add a subquery in a WHERE condition, using hook_views_query_alter()

Question

I tried to add a subquery like this:

function mymodule_views_query_alter(&$view) {
  $view->query->where[1]['conditions'][] = array(
    'field' => 'node.nid',
    'value' => 'SELECT SUBSTRING(source, 6) FROM {url_alias} WHERE alias LIKE "pros/%"',
    'operator' => 'in',
  );
}

I have a specific case where nodes are created with aliases starting with pros/ or not, and I want to get only them.

In a simple raw SQL query, I'd go with this:

SELECT *
FROM node node
WHERE node.nid IN (SELECT SUBSTRING(source, 6)
                   FROM {url_alias}
                   WHERE alias LIKE "pros/%")

But I can't find out how to do this using hook_views_query_alter().

Answer 1

Ideally I think its right to use views_join_subquery. But the subquery you are using is with IN keyword is always slow and will produce same result as using a LEFT JOIN joining with node.nid field.

You can try creating a views_join and adding the url_alias table by add_field method while the node table will be the left table.

I don't have the code at the moment but you can have a look at the below links.
https://stackoverflow.com/questions/6851945/add-table-join-where-and-order-by-to-views-query-in-views-query-alter
http://www.wordpressecoder.com/2014/01/how-to-alter-views-query-with-multiple.html
http://www.yellowpencil.com/blog/last-ditch-fix-programmatically-changing-drupal-7-view

Answer 2

In the end, I went through hook_views_pre_execute() and using a LEFT JOIN..

function mymodule_views_pre_execute($view) {
  $query = $view->build_info['query'];
  $query->leftJoin('url_alias', 'u', 'node.nid = substr(u.source, 6)');
  $query->where("u.alias LIKE 'pros/%'");
}

Something that tripped me up was that I had to use simple quotes around the LIKE, it didn't work with double quotes.

Answer 3

There is a great example of how to do subqueries in the Taxonomy Views integration. You can see it in views_handler_argument_term_node_tid_depth::query().

Ultimately though, the trick is that you build an entire sub-query using db_select(), and then just add that entire resulting object into the Views query object using the add_where() method.

function mymodule_views_query_alter(&$view) {
  $sub_query = db_select('foo', 'f')
    ->fields('f', array('nid'))
    ->condition('f.bar', 'baz');
  $view->query->add_where(0, 'node.nid', $subquery, 'IN');
}

This ability to use a select subquery is documented in the Views add_where() documentation (but unfortunately due to the rendering on api.drupal.org, it doesn't show the example).

You could also probably manually add the where to the $view->query->where[1]['conditions'] array, but using the method instead of manipulating the array directly is recommended.