5

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().

  • I don't know how to do this, but a simpler (& more scalable) alternative might be to use taxonomy. – Andy Jul 24 '14 at 16:00
  • 1
    @Andy this is part of a bigger project, where it's not possible to switch to taxonomy anymore. That said, it wasn't used for other reasons. Thanks for the feedback though. – Florian Margaine Jul 24 '14 at 17:02
2

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

  • 1
    I don't see how joins will help me in this case. Care to elaborate? I've already seen these links, but joins are not what I care about here. – Florian Margaine Jul 24 '14 at 18:47
  • Then I think views_join_subquery is the way to do it. I don't have any working examples at the moment. I'd love to try that one out and I'll post if I make any. – Aneek Mukhopadhyay Jul 25 '14 at 5:15
  • 1
    views_join_subquery does a join. So, no, it doesn't help. – Florian Margaine Jul 25 '14 at 5:56
  • @FlorianMargaine I think the suggestion here is to try SELECT * FROM node JOIN url_alias ON nid = SUBSTR(source, 6) WHERE alias LIKE 'pros/%' – Andy Jul 25 '14 at 11:49
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.

1

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.

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.