0

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?

Improve this question
2
  • 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.
    – Patrick Kenny
    Jul 15, 2021 at 0:58
  • 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
    – mixerowsky
    Jul 15, 2021 at 7:51

1 Answer 1

Reset to default
2

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

Improve this answer
4
  • I don’t need that, this query will work outside the Drupal, as you see it’s direct SQL query
    – mixerowsky
    Jul 14, 2021 at 15:33
  • 2
    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.
    – Beau
    Jul 14, 2021 at 16:18
  • 2
    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.
    – apaderno ♦
    Jul 15, 2021 at 9:40
  • 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.
    – apaderno ♦
    Jul 15, 2021 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.