2

The following (my)?sql query performs a left joins of node table with url_alias table by matching a node with its url alias, and returns rows with the node's title, date created, type, and url-alias:

SELECT n.title, n.created, n.type, ua.alias 
FROM node AS n LEFT JOIN url_alias AS ua 
ON ua.source = CONCAT('node/', n.nid);

I would like to use the drupal DB Interface to accomplish the same thing. Ideally, I would like to write:

$query = db_select( 'node', 'n' )
  ->fields( 'n', Array('created', 'title', 'type' );
$query->leftJoin( 'url_alias', 'ua', 'ua.source = CONCAT("node/", n.nid)');
$query->addField('ua', 'alias');
$results = $query->execute();

which fails with a PDO exception: "node/" is not a valid column (which is true, but I didn't mean it to be a column, hence the CONCAT):

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'node/' in 'on clause': SELECT n.title AS title, n.type AS type, n.created AS created, ua.alias AS alias FROM {node} n LEFT OUTER JOIN {url_alias} ua ON ua.source = CONCAT("node/", n.nid); Array ( ) in _long_and_obscure_named_function() (line 680192318389 of narp.module (it's a large file; I went nuts).)

Rather than feeding the left join query raw into a db_query, is there some way that I can implement the query with db_select and ::leftJoin, etc?

Any help would be much appreciated. (I should add that I am running Drupal 7.x).

4

I've seen this before, I can't remember why it's this way but you can fix it by swapping out which types of quote you use.

Changing your code to this will fix the problem:

$query = db_select( 'node', 'n' )
  ->fields( 'n', array('created', 'title', 'type'));

// Change wrapping quotes to "" and internal to '' for 3rd arg here
$query->leftJoin( 'url_alias', 'ua', "ua.source = CONCAT('node/', n.nid)");

$query->addField('ua', 'alias');
$results = $query->execute();

It's either a Drupal or PDO issue, as either type of quote works fine when executing directly against the MySQL server.

  • BTW, you might actually be better off using db_query() here; db_select() is noticeably slower, and that will get worse by factors as the number of nodes/URLs on the site increases – Clive Oct 22 '13 at 15:49
  • That is good to know, @Clive. Performance is a concern. It is also good to know that the query is also possible with the DB layer. I might look into why there is this quote issue, and get back to this thread (or post a node in drupal.org). – knight Oct 22 '13 at 15:54
  • Cool, I wouldn't mind knowing what's going on there too (not enough to bother looking though :P) – Clive Oct 22 '13 at 15:55
  • 2
    db_select() is slower because of the added OO abstraction. To execute a query object, a query string has to be build from it. But I don't see how it would get worse with the number of rows in the queried tables. Actually, with more rows, the DB will become the slowest part and the performance loss of db_select() will become marginal. So the performance cost of db_select() decreases with the number of nodes and URL aliases. – Pierre Buyle Oct 22 '13 at 16:13
  • @PierreBuyle Well the abstraction cost won't decrease (it'll remain constant), but you're right that it won't increase exponentially like I was suggesting - my mistake. If performance is any sort of concern I always default to db_query(), the gains might be marginal but every little helps (not so much in a single query, but if you've got a lot of them in a single page load it's not the worst idea) – Clive Oct 22 '13 at 16:27

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.