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?

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)
| improve this answer | |
  • 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 '12 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. – Michal Przybylowicz Feb 11 '15 at 11:56
  • How to do this with db_select. – Rajesh Vishwakarma Jul 25 '16 at 6:54

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.