Join Log in
Score:2
avatar Drupal

How do I get the names of the tables where field data is stored?

cn flag
Ryan Hartman

In Drupal 7, one was able to retrieve the physical names of the database tables using some code like the following, it was pretty simple.

$field_definitions = field_info_fields();
foreach ($field_definitions as $field => $definition) {
  $current_storage = $definition['storage']['details']['sql'][FIELD_LOAD_CURRENT];
  $current_table = key($current_storage);
  $revision_storage = $definition['storage']['details']['sql'][FIELD_LOAD_REVISION];
  $revision_table = key($revision_storage);
}

In Drupal 9, the entity API changed for the better and now there are classes and services to pull this type of information. That's all well and good, I understand most of the concepts around this, but I cannot for the life of me seem to extrapolate how to get this same data.

My goal is to loop over all my entity types that implement ContentEntityTypeInterface, get their fields, then build an array looking something like so, I have a very specialized Drush script I'm trying to port over from Drupal 7.

$example = [
  'node' => [
    'field_something' => [
      'current' => 'some_table_name'
      'revision' => 'some_table_name'
    ]
  ],
  'block' => [
    'field_something' => [
      'current' => 'some_table_name'
      'revision' => 'some_table_name'
    ]
  ]
];

*Taking into account whether a field is actually revisionable in the first place, of course.

On my own, I determined for the most part that table names end up being like like $entityType . '__' . $field['name'] and $entityType . '_revision__' . $field['name'] but hardcoding my script breaks down when unique IDs are used. For example, custom blocks have table names like block_content_r__7fe666c7a4. I need to be able to pull that data out of the "field storage definition" of sorts.

Solution: Per @Clive the solution was to get the storage class for each entity type, and then use it to retrieve the table mappings. This works great for non-base fields, but if you wanted a base field the solution would be different. Here is what I ended up with:

$fields = [];
foreach ($this->getContentEntityTypes() as $contentEntityType) {
  $tableMapping = $this->entityTypeManager->getStorage($contentEntityType->id())->getTableMapping();
  foreach ($this->entityFieldManager->getFieldStorageDefinitions($contentEntityType->id()) as $field) {
    // We use requiresDedicatedTableStorage() to filter out base fields 
    if ($tableMapping->requiresDedicatedTableStorage($field)) {
      $fieldInfo = [
        'name' => $field->getName(),
        'type' => $field->getType(),
        'table' => $tableMapping->getDedicatedDataTableName($field)
      ];
      if ($field->isRevisionable()) {
        $fieldInfo['table_revision'] = $tableMapping->getDedicatedRevisionTableName($field);
      }
      $fields[$contentEntityType->id()][] = $fieldInfo;
    }
  }
}
return $fields;

I wrote a separate method to get my content entities, which the above code used.

function getContentEntityTypes() {
  $contentEntityTypes = [];
  $entity_type_definitions = $this->entityTypeManager->getDefinitions();
  /* @var $definition EntityTypeInterface */
  foreach ($entity_type_definitions as $entityType) {
    if ($entityType instanceof ContentEntityTypeInterface && in_array(SqlEntityStorageInterface::class, class_implements($entityType->getStorageClass()))) {
      $contentEntityTypes[] = $entityType;
    }
  }
  return $contentEntityTypes;
}
95
0 + 0
9
Score:4
avatar Drupal
cn flag
Clive

Assuming an SQL storage backend, the table names are available from TableMappingInterface::getAllFieldTableNames(). You can get an entity type's table mapping from its storage handler.

This could probably be more elegant but should be a good starting point:

use Drupal\Core\Entity\ContentEntityTypeInterface;
use Drupal\Core\Entity\Sql\SqlEntityStorageInterface;

...

// These services should be injected if context allows.
$entity_type_manager = \Drupal::entityTypeManager();
/** @var \Drupal\Core\Entity\EntityFieldManagerInterface $field_manager */
$field_manager = \Drupal::service('entity_field.manager');

$tables = [];
foreach ($entity_type_manager->getDefinitions() as $entity_type) {
  // Only list content entity types using SQL storage.
  if ($entity_type instanceof ContentEntityTypeInterface && in_array(SqlEntityStorageInterface::class, class_implements($entity_type->getStorageClass()))) {
    $storage = $entity_type_manager->getStorage($entity_type->id());

    foreach ($field_manager->getFieldStorageDefinitions($entity_type->id()) as $field) {
      $tables[$entity_type->id()][$field->getName()] = $storage->getTableMapping()
        ->getAllFieldTableNames($field->getName());
    }
  }
}

That will get you a result like this:

enter image description here

You'll probably want to do some filtering and make adjustments in the inner foreach loop to get the exact output you need.

0 + 0
cn flag
Ryan Hartman
This is perfect! I knew it was through entity field manager, but wasn't sure how to get from there to the table mapping. I'll try this out on Monday and see how things go! Thanks so much!
0
Reply
cn flag
Ryan Hartman
I took a quick look at this and it worked. I'd assume I could ask the definition of the field if it was revisionable, and then there is a method to get that table name?
0
Reply
cn flag
Clive
You can ask whether it's revisionable with `$field->isRevisionable()`, but getting the exact table name is more complicated as base fields can exist in multiple tables. If you just need non-base fields, or more accurately those that don't live in the base and base revision tables, you can use `$storage->getTableMapping()->requiresDedicatedTableStorage($field)` to filter candidates, then use `$storage->getTableMapping()->getDedicatedDataTableName($field)` and `->getDedicatedRevisionTableName($field)` to get the table names
1
Reply
cn flag
Ryan Hartman
Yup, that worked perfectly. My final code has been added to the question. Thanks again for the help!
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.