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?

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. Jul 15 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 at 7:51
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

4
  • I don’t need that, this query will work outside the Drupal, as you see it’s direct SQL query
    – mixerowsky
    Jul 14 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 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 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 at 9:45

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.