How to use a referenced entity's values in a TableSort?

Question

I have a custom entity with a field which is defined as follows.

$fields['file'] = BaseFieldDefinition::create('file')
  ->setDescription('Reference to the built-in core File entity type.')
  ->setLabel('File')
  ->setRequired(TRUE)
  ->setSetting('file_extensions', 'pdf rtf doc docx')
  ->setSetting('max_filesize', '20MB')
  ->setSetting('description_field', TRUE)
  ->setDisplayOptions('form', ['type' => 'file'])
  ->setDisplayOptions('view', ['type' => 'file']);

I have no problem getting to the fields of the referenced File entity from instances of my own entity type. So for example, these lines work as expected.

$filename = $my_entity->file->entity->filename->value;
$username = $my_entity->file->entity->uid->entity->name->value;

When I try to use the fields of the referenced File entity with a TableSort, which I want to plug into an entity query on my custom entity type, the code throws an exception.

$header['name'] = [
  'data' => 'File Name',
  'field' => 'file.entity.filename',
  'specifier' => 'file.entity.filename',
];
$header['user'] = [
  'data' => 'Uploaded By',
  'field' => 'file.entity.uid.entity.name',
  'specifier' => 'file.entity.uid.entity.name',
];

// …

$query->tableSort($header);

Uncaught PHP Exception Drupal\Core\Entity\Query\QueryException: "'file' not found"

For the syntax of the $header array I'm largely relying on clues I got from Tablesort with EntityQuery, in which Berdir recommends that we look at the load() and buildHeader() methods of the UserListBuilder class. Unfortunately, that example appears to be using field values which are stored directly in the base table, so I'm falling back on the assumption that, since we're in entity query land, the same syntax described in QueryInterface::condition() would be expected.

I've looked at a number of the open issues, such as Table sort ignores "field", always adds header title as order query parameter and Tablesort cleanup, but I didn't see anything which shed any light on the problem.

Answer 1

There's a bug in core, and for the time being a workaround is:

'specifier' => 'file.target_id.entity.filename',

The reason for this is the entity query relationship syntax started out as join support, it predates the entity reference support in core and as such while $entity->file->target_id->entity is not supported by core, file.target_id.entity is supported by entity query to indicate it should join the entity specified by the file field and the entity id should be what is specified by target_id. It's a bit clunky but at the time, it was what worked.

It should be able to figure out file.entity.filename as well because the main property name for the file field happens to be target_id and so it is able to expand it for you into file.target_id.entity.filename. However, the class in case Drupal\Core\Entity\Query\Sql\Tables is extremely convoluted and this expansion has a bug for the shared table case which will be fixed in https://www.drupal.org/project/drupal/issues/3278083

Answer 2

The failure described above appears to be the result of a bug in the FileItem plugin. Even though the field type plugin claims it is an entity reference (the @FieldType annotation says category = @Translation("Reference")), and in all other respects that is true, the support for the documented entity query syntax is broken.

Until the bug is fixed, the workaround would be to give up the specialization implemented by the FileItem field type and use a plain entity_reference field instead. With that change the TableSort code above works correctly.