0

Joining the node table to the url_alias table requires CONCAT. Here is an example of how this can be done:

$results = $db->query("
  SELECT 
    n.nid, n.title, 
    a.source, a.alias 
  FROM {node} n 
  LEFT JOIN {url_alias} a 
    ON CONCAT('node/', nid) = source
  ")->fetchAll();

The above query is taken from How to map url_alias table to node table?

How can I do the same thing with a dynamic query?

My Drupal version is 10.1.5, but the answer is likely the same for Drupal 8 and 9.

7
  • Could you clarify what you mean by "dynamic query", and which version of Drupal this is about? db_query is from Drupal 7 so assuming that, but "dynamic" might mean a bunch of different things in any version
    – Clive
    Nov 7, 2023 at 14:55
  • I updated the question to specify Drupal 10 and I added a link to the docs for Drupal's dynamic queries.
    – arnoldbird
    Nov 7, 2023 at 15:17
  • Thanks, example here: drupal.stackexchange.com/questions/208295/writing-a-left-join. If that doesn't do the trick please update this question with details of what you've tried and what's happening different to what you expect - otherwise we can probably close this as a duplicate
    – Clive
    Nov 7, 2023 at 15:45
  • Thanks for the response, but that topic doesn't address the question here. It does not show how to use CONCAT -- combined with a join -- in a dynamic query, so it is not of any use for joining the node table with the url_alias table. I'm familiar with how to do joins in a dynamic query, and also familiar with how to use CONCAT in a dynamic query -- but am not familiar with how to combine the two.
    – arnoldbird
    Nov 7, 2023 at 17:10
  • 1
    Which bit of the join are you unable to produce with the linked post? It’s all strings so just put the CONCAT in the third parameter. Are you struggling to get the correct table or field alias maybe? If not it’s literally just copy-pasting everything after ON in your SQL version above into that 3rd param
    – Clive
    Nov 7, 2023 at 18:06

1 Answer 1

0

The following dynamic query can use to join node with path_alias table: (Drupal 8/9/10)

 $query = \Drupal::database()->select('node', 'n');
  $query->join('path_alias', 'pa', 'CONCAT(\'/node/\', n.nid) = pa.path');
  $rst = $query->fields('pa')
      ->execute()
      ->fetchAll();

Note: In Drupal(8/9/10) path_alias table can use to get path alias maintained for node

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.