Drupal 7 functions during JOINs / matching the URL alias with the node ID using a dynamic query

While Drupal 7 dynamic query JOINs do accept functions (CONCAT at least), I had a tough time trying to figure out how to get them to work.

In my case I wanted to pull the URL alias out of the database along with a bunch of other information on a handful of nodes (don’t use this for lots of nodes, if you can help it, as MySQL performance will not be good).  This is easy if you already have the node ID before starting the query, however it can be tricky if you don’t as the node ID is not in the url_alias table.

The only way I could get it to work was like this:

$query->join('url_alias', 'ua', 'ua.source = CONCAT(\'node/\',n.nid)');

As you’ll notice, this will only work with taking out aliases for nodes, unfortunately.

I also tried the following which DO NOT WORK:

// substring instead of concat
$query->join('url_alias', 'ua', 'n.nid = SUBSTRING(ua.source FROM \'/\')');
// another version of substring
$query->join('url_alias', 'ua', 'n.nid = SUBSTRING(ua.source,5)');
// and variations on the above, adding single, escaped single and double quotes in various places

// concat using double quotes
$query->join('url_alias', 'ua', 'ua.source = CONCAT("node/",n.nid)');

// using concat / substring in placeholders
$query->join('url_alias', 'ua', 'ua.source = :node_source', array(':node_source' => 'CONCAT(\'node/\', n.nid)'));
$query->join('url_alias', 'ua', 'n.nid = :node_source', array(':node_source' => 'SUBSTRING(ua.source,5)'));
// and variations on the above, using single/escaped single/double quotes, and different versions of SUBSTRING

// using the addExpression with a join
$query->addExpression('CONCAT(:source_pre,n.nid)', 'node_source', array(':source_pre' => 'node/'));
$query->addExpression('CONCAT(\'node/\',n.nid)', 'node_source');
$query->join('url_alias', 'ua', ':node_source = ua.source', array(':node_source' => 'node_source'));
$query->join('url_alias', 'ua', 'node_source = ua.source');

// I even tried adding an AND to the join, hoping it had some strange and magical usage
$query->join('url_alias', 'ua', 'n.nid = ua.source AND ua.source = :node_source', array(':node_source' => 'CONCAT(\'node/\', n.nid)'));

I’m sure there are some syntax issues with some of the above examples, but it still seems the dynamic database API is pretty rigid!

Leave a Reply

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