I was trying to use some SQL to map the url_alias field to my node field on the url_alias.pid = node.nid field. However, it turns out that these are not apparently related, sometimes they are the same value and sometimes they aren't. As I'm looking through my mysql db I can see no connection between the url_alias table and the node table.

The url alias is coming from the pathauto module. I just want to create links to my resources when the titles occur in a sql query in a custom module...this seemed the easiest way to do it.

Does anyone have any idea on which fields or tables the connections occur between these two entities?

  • Update: the only connection I see is that the url_alias table has a field called 'source' with node/35 (for example) which does map, technically I suppose to the node table as it would be nid 35. Maybe what I need is a SQL-based way to parse node/35 so I can compare it with n.nid=35. Thoughts? – Sage Mar 13 '15 at 7:34

They are not related because url_alias table contains all the URL aliases. You can use MySQL CONCAT to concatenate strings (fields or string literals).

$results = db_query("
    n.nid, n.title, 
    a.source, a.alias 
  FROM {node} n 
  LEFT JOIN {url_alias} a 
    ON CONCAT('node/', nid) = source
foreach ($results as $result) {
| improve this answer | |

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.