Module Entity Reference Revisions is used with paragraph entities from modules
Paragraphs →
. Using them through views leads some time to SQL error like,
Drupal\Core\Database\DatabaseExceptionWrapper: Exception in <View>[<View>]: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ...
That is formation of a defective SQL request. Table column is missing: is ''.
Adding convenient column name in the wrong given SQL request allows to obtain a valid request.
The problem appends with an $entity_type_id user and $field_name (field) of type entity reference revisions (module : entity_reference_revisions) with reference type: Paragraph.
The problem disappears with one change in entity_reference_revisions/entity_reference_revisions.views.inc line 58 in the setting of $data[$target_base_table][$pseudo_field_name]['relationship'],
'base field' => $entity_type->getKey('revision'),
->
'base field' => $entity_type->getKey('revision') ?: $entity_type->getKey('id'),
The code context is,
$data[$target_base_table][$pseudo_field_name]['relationship'] = array(
'title' => t('@entity using @field_name', $args),
'label' => t('@field_name', array('@field_name' => $field_name)),
'group' => $target_entity_type->getLabel(),
'help' => t('Relate each @entity with a @field_name set to the @label.', $args),
'id' => 'entity_reverse',
'base' => $entity_type->getDataTable() ?: $entity_type->getBaseTable(),
'entity_type' => $entity_type_id,
'base field' => $entity_type->getKey('revision'),
'field_name' => $field_name,
'field table' => $table_mapping->getDedicatedDataTableName($field_storage),
'field field' => $field_name . '_target_revision_id',
'join_extra' => array(
array(
'field' => 'deleted',
'value' => 0,
'numeric' => TRUE,
),
),
);
About function getKeys() or getKey(), in core/lib/Drupal/Core/Entity/EntityTypeInterface.php,
/**
* Gets an array of entity keys.
*
* @return array
* An array describing how the Field API can extract certain information
* from objects of this entity type:
* - id: The name of the property that contains the primary ID of the
* entity. Every entity object passed to the Field API must have this
* property and its value must be numeric.
* - revision: (optional) The name of the property that contains the
* revision ID of the entity. The Field API assumes that all revision IDs
* are unique across all entities of a type. If this entry is omitted
* the entities of this type are not revisionable.
...
*/
public function getKeys();Therefore for an entity type, revision could not exist, and $entity_type->getKey('revision') return ''.
Moreover, in core/modules/views/src/Plugin/views/relationship/EntityReverse.php,
/**
* Called to implement a relationship in a query.
*/
public function query() {
...
// Second, relate the field table to the entity specified using
// the entity id on the field table and the entity's id field.
$second = [
'left_table' => $this->first_alias,
'left_field' => 'entity_id',
'table' => $this->definition['base'],
'field' => $this->definition['base field'],
'adjusted' => TRUE,
];Therefore, since the 'base field' could be '' also the field/column's table.
Remark: same problem may also exist with the other line, 39,
'base field' => $target_entity_type->getKey('revision'),
but never met. Waiting for some errors before some change here.
entity_reference_revisions/entity_reference_revisions.views.inc line 58,
'base field' => $entity_type->getKey('revision'),
->
'base field' => $entity_type->getKey('revision') ?: $entity_type->getKey('id'),
Remark: another change could be,
'base field' => $entity_type->getKey('id'),
But the first change means use revision while it is possible, and if not use id instead.
The second change means use the always existing id, rather the sometime existing revision.
Since the module is about revision the first one should be better. Even if in the core for
'id' => 'entity_reverse' it is always 'base field' => $entity_type->getKey('id'). See in core/,
modules/views/views.views.inc:828: 'base field' => $entity_type->getKey('id'),
modules/views/views.api.php:575: 'base field' => $entity_type->getKey('id'),
modules/views/views.api.php:632: 'base field' => $entity_type->getKey('id'),Needs review
1.10
Code
Not all content is available!
It's likely this issue predates Contrib.social: some issue and comment data are missing.
Here is a patch for this problem for version 8.x-1.10.
This might be fixed already in 🐛 Views doesn't recognize relationship to host Needs review