sqldrupaldrupal-9

How do I get a node by its path_alias? And a path_alias by a node?

I'm trying to get raw data from a Drupal 9 install and I'm just looking for plain SQL to get a node by its path alias:

+--------+-------------+--------------------------------------+----------+-------------+-------------------+--------+
| id     | revision_id | uuid                                 | langcode | path        | alias             | status |
+--------+-------------+--------------------------------------+----------+-------------+-------------------+--------+
| 222385 |      185331 | 1086ef60-2dd5-4fce-a8b4-1fe4eb9fe5af | und      | /node/25077 | /category/hello   |      1 |
+--------+-------------+--------------------------------------+----------+-------------+-------------------+--------+

What I'm not sure of is how the path_alias relates to the node. How do I get a node by its path and also the path by node? Do one of those ids relate to a pivot table like:

path_alias->pivot(id)->node?

If so what relational table is that?

I'm just looking for raw:

SELECT * FROM node WHERE [id] = 'this-id-from route alias';

I have tried using the path_alias uuid:

SELECT *
FROM node
WHERE uuid = '1086ef60-2dd5-4fce-a8b4-1fe4eb9fe5af';

With no success. I'm not sure what the relation is between the two when I know for certain there is one.

Solution

  • try this solution.

    All Entries

    SELECT node.nid, path_alias.path, path_alias.alias FROM `node`
LEFT JOIN path_alias ON CONCAT('/node/', node.nid) = path_alias.path;

    Results

    +-----+---------+-------------------------+
| nid | path    | alias                   |
+-----+---------+-------------------------+
|   1 | /node/1 | /basic-page/test-page-1 |
|   2 | /node/2 | /basic-page/test-page-2 |
|   3 | /node/3 | /article/test-page-3    |
|   4 | /node/4 | /article/test-page-4    |
|   5 | NULL    | NULL                    |
+-----+---------+-------------------------+

    Search by Node ID

    SELECT node.nid, path_alias.path, path_alias.alias FROM `node`
LEFT JOIN path_alias ON CONCAT('/node/', node.nid) = path_alias.path
WHERE node.nid = 2;

    Results

    +-----+---------+-------------------------+
| nid | path    | alias                   |
+-----+---------+-------------------------+
|   2 | /node/2 | /basic-page/test-page-2 |
+-----+---------+-------------------------+

    Search by Alias

    SELECT node.nid, path_alias.path, path_alias.alias FROM `node`
LEFT JOIN path_alias ON CONCAT('/node/', node.nid) = path_alias.path
WHERE path_alias.alias = '/article/test-page-4';

    Results

    +-----+---------+-------------------------+
| nid | path    | alias                   |
+-----+---------+-------------------------+
|   4 | /node/4 | /article/test-page-4    |
+-----+---------+-------------------------+

    With Select ALL

    SELECT * FROM `node`
LEFT JOIN path_alias ON CONCAT('/node/', node.nid) = path_alias.path;

    Results

    +-----+------+---------+--------------------------------------+----------+------+-------------+--------------------------------------+----------+---------+-------------------------+--------+
| nid | vid  | type    | uuid                                 | langcode | id   | revision_id | uuid                                 | langcode | path    | alias                   | status |
+-----+------+---------+--------------------------------------+----------+------+-------------+--------------------------------------+----------+---------+-------------------------+--------+
|   1 |    1 | page    | b3276c0c-76c4-4e1c-93e8-198acbb1626b | de       |  467 |         467 | bfd389f1-9020-4316-bae8-6186e2c2106a | de       | /node/1 | /basic-page/test-page-1 |      1 |
|   2 |    2 | page    | a72dd325-c994-4a33-b67b-7cb47dbc6d54 | de       |  468 |         468 | f7e4ae6b-eb67-4b55-9578-7cfa56279344 | de       | /node/2 | /basic-page/test-page-2 |      1 |
|   3 |    3 | article | 4b57180d-9826-46a9-9d54-b5b8b4fb713b | de       |  469 |         469 | ff3d8d47-b710-4eb7-8397-7312bc92a70f | de       | /node/3 | /article/test-page-3    |      1 |
|   4 |    4 | article | 858d7100-244b-4e05-b13e-d55966cb86fe | de       |  470 |         470 | ffbab5c1-0157-43e8-9aa1-93460cb79b42 | de       | /node/4 | /article/test-page-4    |      1 |
|   5 |    5 | page    | e0c56917-feac-4dd0-bd08-f5e8f67f40fa | de       | NULL |        NULL | NULL                                 | NULL     | NULL    | NULL                    |   NULL |
+-----+------+---------+--------------------------------------+----------+------+-------------+--------------------------------------+----------+---------+-------------------------+--------+

    PS: NID 5 shows a node without an path_alias entry