Join Log in
Score:0
avatar Drupal

Get path (alias) from SQL query

in flag
mixerowsky

I have a SQL query to get all nodes with their ID, title, publish date, change date, etc.

Additionally I need to get path to each of them (probably from path_alias.alias).

Now I have following (without alias):

SELECT MIN(node_field_data.nid) AS nid,node_field_data.type, node_field_data.status, node_field_data.title, node_field_data.langcode AS node_field_data_langcode, node_field_data.created, node_field_data.changed
FROM
node_field_data
INNER JOIN node ON node_field_data.nid = node.nid
GROUP BY node.nid, node_field_data.changed, node_field_data_langcode
ORDER BY node_field_data.nid DESC;

Any idea how to get it?

148
1 + 2
8
cn flag
Patrick Kenny
As a quick and dirty way to get queries, you can build a view that does what you want and then turn on "show query SQL" in the Views admin options.
0
Reply
in flag
mixerowsky
yes, it's what I did, but extended this query a bit since I need to save results in CSV...Views giving a bit different query than I need
0
Reply
Score:2
avatar Drupal
in flag
Beau

If you want to access Drupal entities, then you should use EntityTypeManager's getStorage() method to return a storage controller for nodes:

// Get a storage handler reference for Nodes.
$node_storage = \Drupal::entityTypeManager()->getStorage("node");
// Load an arbitrary node, as identified by the $nid variable.
$node = $node_storage->load($nid);

Being that a node is a type of content entity, you'll have the entire API as defined by Drupal\node\NodeInterface, to work with. This includes the ability to call:

// Get internal path, path alias if exists, for an entity.
$node->toUrl()->toString();

For more insight on how to work with Entities in Drupal, please see this post, from which I borrowed the above example: https://www.drupal.org/docs/drupal-apis/entity-api/working-with-the-entity-api

0 + 4
in flag
mixerowsky
I don’t need that, this query will work outside the Drupal, as you see it’s direct SQL query
0
Reply
in flag
Beau
I understand completely. :) As it turns out, it is considered best practice to use the entity layer in order to manage, query and edit entities in Drupal. In bypassing the designated sub-systems, you'll be fighting an uphill battle, in which you are likely to need to load Node entities anyway, in order to achieve what you are asking. Additionally, things like internal property values and table names are not considered guaranteed and could change unexpectedly. The API is guaranteed; the implementation is not.
2
Reply
apaderno avatar
us flag
apaderno
On Drupal 8, path aliases are now entities too. Even if nodes and path aliases weren't entities, it would be always better to use the existing API, rather than querying the Drupal database directly. Drupal can expose a REST API too, which makes possible to query entities without knowing any detail on which database tables are used for the purpose, or which fields those tables contain.
2
Reply
apaderno avatar
us flag
apaderno
Yes, I would rather run PHP code to do that task than querying the database from a MySQL terminal. This requires to bootstrap Drupal, if the PHP code isn't for a Drupal module, but it's an external script that access Drupal data. (It's external from the Drupal point-of-view.) Alternatively, I would use the REST API Drupal implements if the information is necessary in code that isn't written in PHP.
0
Reply
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.