3

For some backups and exports, I need to fetch the URL-aliases for each node in SQL. The url_alias taqble, however, has no foreign key to the node table (nid) nor the other way around, it seems.

Is there some hidden join-table somewhere? A neat trick to fetch the node and its alias in a JOIN?

1 Answer 1

7

Since the value in the src column in the url_alias table is predictable for nodes you can just query it directly, without the need for any joins:

$nid = 1; // node id
$src = 'node/' . $nid;
$query = db_query("SELECT dst FROM {url_alias} WHERE src = '%s'", $src);

If you definitely want to use a join though, something like this will work:

SELECT n.nid, ua.dst
FROM {node} n 
INNER JOIN {url_alias} ua ON ua.src = CONCAT('node/', n.nid)
3
  • 1
    Thanks, the concat in the ON is exactly what I was looking for. Performs horribly, but not a huge problem here.
    – berkes
    Jul 3, 2012 at 11:12
  • I am using Drupal 7 and there is no such field ua.src I needed to change it to this: INNER JOIN {url_alias} ua ON ua.source = CONCAT('node/', n.nid), and now it works. Feb 11, 2015 at 11:56
  • How to do this with db_select. Jul 25, 2016 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.