2

In Drupal 7 I had this code to query node fields and its respective title:

$query = db_select('node', 'n');
$query->join('field_data_field_internal_institute_paper_i', 'iipi', 'n.nid = iipi.entity_id');
$query->fields('n', array('nid'));
// some other conditions
$query->fields('n', array('title')); // <- working fine for D7
$result = $query->execute()->fetchall();

which was working fine. I know imported to D9 and I need to rewrite this accordingly:

$db = \Drupal::database();
$query = $db->select('node', 'n');
$query->join('node_revision__field_internal_institute_paper_i', 'iipi', 'n.nid = iipi.entity_id');
$query->fields('n', array('nid'));
// some other conditions
$query->fields('n', array('title')); //<- not working for D9
$result = $query->execute()->fetchall();

for some reason array('title') isn't a field anymore as seen above. How can I get the title here?

2
  • 1
    I think you need to provide a little bit more detail, because that query should work fine in Drupal 9. Do you have an error that you can share? Or are you just not getting any results back? I also don't see why you are joining the revision table without a WHERE condition, so if not needed, just grab the titles from the node_field_data table as you say.
    – baikho
    Nov 30 '20 at 17:58
  • 1
    @baikho I managed to make this work via node_field_data. This was different in D7 as I mentioned in my commend above.
    – supersize
    Nov 30 '20 at 18:03
2

This works fine in Drupal 9 for titles only from the node_field_data table:

$ drush php
Psy Shell v0.10.4 (PHP 7.4.13 — cli) by Justin Hileman
>>> $db = \Drupal::database();
=> Drupal\Core\Database\Driver\mysql\Connection {#767
     +"_serviceId": "database",
   }
>>> $query = $db->select('node_field_data', 'n');
=> Drupal\Core\Database\Query\Select {#4281}
>>> $query->fields('n', ['title']);
=> Drupal\Core\Database\Query\Select {#4281}
>>> $result = $query->execute()->fetchall();
=> [
     {#4254
       +"title": "Foo",
     },
     {#4257
       +"title": "Bar",
     },
   ]

Or for only an array of titles use fetchCol():

>>> $result = $query->execute()->fetchCol();
=> [
     "Foo",
     "Bar",
   ]

Node Field Data table:

mysql> describe node_field_data;
+-------------------------------+------------------+------+-----+---------+-------+
| Field                         | Type             | Null | Key | Default | Extra |
+-------------------------------+------------------+------+-----+---------+-------+
| nid                           | int(10) unsigned | NO   | PRI | NULL    |       |
| vid                           | int(10) unsigned | NO   | MUL | NULL    |       |
| type                          | varchar(32)      | NO   | MUL | NULL    |       |
| langcode                      | varchar(12)      | NO   | PRI | NULL    |       |
| status                        | tinyint(4)       | NO   | MUL | NULL    |       |
| uid                           | int(10) unsigned | NO   | MUL | NULL    |       |
| title                         | varchar(255)     | NO   | MUL | NULL    |       |
| created                       | int(11)          | NO   | MUL | NULL    |       |
| changed                       | int(11)          | NO   | MUL | NULL    |       |
| promote                       | tinyint(4)       | NO   | MUL | NULL    |       |
| sticky                        | tinyint(4)       | NO   |     | NULL    |       |
| default_langcode              | tinyint(4)       | NO   |     | NULL    |       |
| revision_translation_affected | tinyint(4)       | YES  |     | NULL    |       |
+-------------------------------+------------------+------+-----+---------+-------+
13 rows in set (0.00 sec)
1
  • 1
    Thanks for the answer. I have found this but it will be helpful for other people to know. I didn't know that field has changed. In D7 the title was on the node field.
    – supersize
    Nov 30 '20 at 18:04
0

If you want to get a node entity, you can use the loadByProperties method in the node storage.

  $nodes = \Drupal::entityTypeManager()
      ->getStorage('node')
      ->loadByProperties(['title' => $title]);
    // Load the first node returned from the database.
    $node = reset($nodes);
1
  • Thanks for your commend. I want to do this in a DB query, not via entityTypeManager.
    – supersize
    Dec 1 '20 at 9:16

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.