SQL left join query using Drupal DB Layer

Question

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

Answer 1

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.