Problem/Motivation

Adding a views relationship to a field that references a config entity does not work.

Proposed resolution

Remove the option to create a views relationship with the config entity as there is no base table to join.

Remaining tasks

Add an update path to refresh the views data.

API changes

API clean up, a user can't add a relationship to config entity via UI anymore.

Data model changes

None.

Release notes snippet

N/A

------
Original IS:

Dears,
I am trying to use contextual filters with relationships and have the error below (with the relationships):

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'movadomfpadrupal._taxonomy_term__field_domaine' doesn't exist: SELECT node_field_data.langcode AS node_field_data_langcode, node_field_data.title AS node_field_data_title, node_field_data.nid AS nid, taxonomy_term_field_data_node__field_population.tid AS taxonomy_term_field_data_node__field_population_tid FROM {node_field_data} node_field_data LEFT JOIN {node__field_population} node__field_population ON node_field_data.nid = node__field_population.entity_id AND (node__field_population.deleted = :views_join_condition_0 AND node__field_population.langcode = node_field_data.langcode) LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node__field_population ON node__field_population.field_population_target_id = taxonomy_term_field_data_node__field_population.tid LEFT JOIN {taxonomy_term__field_domaine} taxonomy_term_field_data_node__field_population__taxonomy_term__field_domaine ON taxonomy_term_field_data_node__field_population.tid = taxonomy_term_field_data_node__field_population__taxonomy_term__field_domaine.entity_id AND (taxonomy_term_field_data_node__field_population__taxonomy_term__field_domaine.deleted = :views_join_condition_2 AND taxonomy_term_field_data_node__field_population__taxonomy_term__field_domaine.langcode = taxonomy_term_field_data_node__field_population.langcode) LEFT JOIN {} _taxonomy_term__field_domaine ON taxonomy_term_field_data_node__field_population__taxonomy_term__field_domaine.field_domaine_target_id = _taxonomy_term__field_domaine.id WHERE (( (node_field_data.status = :db_condition_placeholder_4) AND (node_field_data.type IN (:db_condition_placeholder_5)) )) ORDER BY node_field_data_title ASC; Array ( [:db_condition_placeholder_4] => 1 [:db_condition_placeholder_5] => service [:views_join_condition_0] => 0 [:views_join_condition_2] => 0 )

I have services contents associated with taxonomy terms (populations) using an entity reference field (field_population).
"Populations" taxomony are associated to domain entries (https://www.drupal.org/project/domain) using a reference field (field_domaine).

In my view (called "services" which displays services contents) when I add the relationship to the "Populations" taxonomy terms, everyhtning goes well.
When I add the relationship to the reference to a domain field, I go the error.
What am I doing wrong ?

View details is in attachment

Thanks for your help?

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

superlolo95 created an issue. See original summary.

Lendude’s picture

Title: SQLSTATE[42S02]: Base table or view not found » Views throws 'SQLSTATE[42S02]: Base table or view not found' due to malformed join on conditional filters that use a relationship
Project: Views (for Drupal 7) » Drupal core
Version: 8.x-3.x-dev » 8.3.x-dev
Component: Code » views.module
Issue tags: +Needs steps to reproduce

Moving to the Views core queue for now, might need to go to the Domain queue.

LEFT JOIN {} _taxonomy_term__field_domaine

For some reason it seems to be trying to join an empty string as the table.

We need some steps to reproduce for this, not sure if this is a Views thing or a Domain thing.

dawehner’s picture

Status: Active » Postponed (maintainer needs more info)

Let's set (postponed (maintainer needs more info)).

superlolo95’s picture

Dears,
After further testing.
Test 1 passed

  1. Create a view to display articles
  2. Create a taxomony dictionnary
  3. Create a new field for the taxonomy dictionnary that is a reference to a content
  4. Add the relationship to the taxonomy dictionnary previously created
  5. add the relationship to the [reference to a content] field previously created

Test 2 passed

  1. Create a view to display articles
  2. Create a taxomony dictionnary
  3. Create a new field for the taxonomy dictionnary that is a reference to an uimage
  4. Add the relationship to the taxonomy dictionnary previously created
  5. add the relationship to the [reference to an image] field previously created

Test 3 failed

  1. Create a view to display articles
  2. Create a taxomony dictionnary
  3. Create a new field for the taxonomy dictionnary that is a reference to a role (field_reference)
  4. Add the relationship to the taxonomy dictionnary previously created
  5. add the relationship to the [reference to a role] field previously created

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'movadomfpadrupal._taxonomy_term__field_reference' doesn't exist: SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {node_field_data} node_field_data LEFT JOIN {node__field_population} node__field_population ON node_field_data.nid = node__field_population.entity_id AND (node__field_population.deleted = :views_join_condition_0 AND node__field_population.langcode = node_field_data.langcode) LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node__field_population ON node__field_population.field_population_target_id = taxonomy_term_field_data_node__field_population.tid LEFT JOIN {taxonomy_term__field_reference} taxonomy_term_field_data_node__field_population__taxonomy_term__field_reference ON taxonomy_term_field_data_node__field_population.tid = taxonomy_term_field_data_node__field_population__taxonomy_term__field_reference.entity_id AND (taxonomy_term_field_data_node__field_population__taxonomy_term__field_reference.deleted = :views_join_condition_2 AND taxonomy_term_field_data_node__field_population__taxonomy_term__field_reference.langcode = taxonomy_term_field_data_node__field_population.langcode) LEFT JOIN {} _taxonomy_term__field_reference ON taxonomy_term_field_data_node__field_population__taxonomy_term__field_reference.field_reference_target_id = _taxonomy_term__field_reference.id WHERE (( (node_field_data.type IN (:db_condition_placeholder_0)) ))) subquery; Array ( [:db_condition_placeholder_0] => article [:views_join_condition_0] => 0 [:views_join_condition_2] => 0 )

Test 4 failed

  1. Create a view to display articles
  2. Create a taxomony dictionnary
  3. Create a new field for the taxonomy dictionnary that is a reference to a block (field_reference_to_block)
  4. Add the relationship to the taxonomy dictionnary previously created
  5. add the relationship to the [reference to a block] field previously created

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'movadomfpadrupal._taxonomy_term__field_reference_to_block' doesn't exist: SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {node_field_data} node_field_data LEFT JOIN {node__field_population} node__field_population ON node_field_data.nid = node__field_population.entity_id AND (node__field_population.deleted = :views_join_condition_0 AND node__field_population.langcode = node_field_data.langcode) LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node__field_population ON node__field_population.field_population_target_id = taxonomy_term_field_data_node__field_population.tid LEFT JOIN {taxonomy_term__field_reference_to_block} taxonomy_term_field_data_node__field_population__taxonomy_term__field_reference_to_block ON taxonomy_term_field_data_node__field_population.tid = taxonomy_term_field_data_node__field_population__taxonomy_term__field_reference_to_block.entity_id AND (taxonomy_term_field_data_node__field_population__taxonomy_term__field_reference_to_block.deleted = :views_join_condition_2 AND taxonomy_term_field_data_node__field_population__taxonomy_term__field_reference_to_block.langcode = taxonomy_term_field_data_node__field_population.langcode) LEFT JOIN {} _taxonomy_term__field_reference_to_block ON taxonomy_term_field_data_node__field_population__taxonomy_term__field_reference_to_block.field_reference_to_block_target_id = _taxonomy_term__field_reference_to_block.id WHERE (( (node_field_data.type IN (:db_condition_placeholder_0)) ))) subquery; Array ( [:db_condition_placeholder_0] => article [:views_join_condition_0] => 0 [:views_join_condition_2] => 0 )

I suppose that if the reference is not a a reference to somathing is categorized as "Content" (but something that is categorized as a "Configuration"), it fails.
It seems not related to the domain module but to views.

Regards

superlolo95’s picture

Version: 8.3.x-dev » 8.4.x-dev

Drupal 8.3.0-alpha1 will be released the week of January 30, 2017, which means new developments and disruptive changes should now be targeted against the 8.4.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.4.x-dev » 8.5.x-dev

Drupal 8.4.0-alpha1 will be released the week of July 31, 2017, which means new developments and disruptive changes should now be targeted against the 8.5.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.5.x-dev » 8.6.x-dev

Drupal 8.5.0-alpha1 will be released the week of January 17, 2018, which means new developments and disruptive changes should now be targeted against the 8.6.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.6.x-dev » 8.7.x-dev

Drupal 8.6.0-alpha1 will be released the week of July 16, 2018, which means new developments and disruptive changes should now be targeted against the 8.7.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

dchaffin’s picture

I'm having this same issue as well ... trying to create a relationship to a domain access record.

JOIN {} _commerce_store__field_store_domain

Version: 8.7.x-dev » 8.8.x-dev

Drupal 8.7.0-alpha1 will be released the week of March 11, 2019, which means new developments and disruptive changes should now be targeted against the 8.8.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.8.x-dev » 8.9.x-dev

Drupal 8.8.0-alpha1 will be released the week of October 14th, 2019, which means new developments and disruptive changes should now be targeted against the 8.9.x-dev branch. (Any changes to 8.9.x will also be committed to 9.0.x in preparation for Drupal 9’s release, but some changes like significant feature additions will be deferred to 9.1.x.). For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

Version: 8.9.x-dev » 9.1.x-dev

Drupal 8.9.0-beta1 was released on March 20, 2020. 8.9.x is the final, long-term support (LTS) minor release of Drupal 8, which means new developments and disruptive changes should now be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

pameeela’s picture

Title: Views throws 'SQLSTATE[42S02]: Base table or view not found' due to malformed join on conditional filters that use a relationship » Unable to add relationship to config entity in view
Issue summary: View changes
Status: Postponed (maintainer needs more info) » Active
FileSize
147.56 KB

Updating the IS based on #4 where steps to reproduce were provided.

pameeela’s picture

Issue summary: View changes
pameeela’s picture

Title: Unable to add relationship to config entity in view » Unable to add relationship to a field referencing a config entity in view
Lendude’s picture

Lendude’s picture

Priority: Normal » Major
Status: Active » Needs review
FileSize
1.65 KB
6.8 KB

Here we go with a 'fix'. This is in line with what is being done in \Drupal\views\EntityViewsData::processViewsDataForEntityReference, why is this done twice I hear you thinking...that is an excellent question, but out of scope here I think.

Since there is no real way to fix this (there is after all nothing to join on), the best we can do is remove the relationship.

If tested the effect this has on existing relationships and it goes from throwing an SQL error to having a Broken/missing handler.

So we could add an upgrade path that cleans up these relationships, but I think that we can be pretty sure this is not being used in the wild, well not successfully anyway....

Bumping to major because it leads to a fatal error through normal use of the UI.

The last submitted patch, 18: 2838555-18-TEST_ONLY.patch, failed testing. View results
- codesniffer_fixes.patch Interdiff of automated coding standards fixes only.

Lendude’s picture

+++ b/core/modules/views/views.views.inc
@@ -505,7 +506,7 @@ function views_field_default_views_data(FieldStorageConfigInterface $field_stora
-  list($label, $all_labels) = views_entity_field_label($entity_type_id, $field_name);
+  [$label, $all_labels] = views_entity_field_label($entity_type_id, $field_name);

no idea where this bit came from, but it's unrelated

Lendude’s picture

jibran’s picture

Title: Unable to add relationship to a field referencing a config entity in view » Don't add relationship to a field referencing a config entity
Issue tags: +DER issue, +Bug Smash Initiative

This is not major IMO, even if it is fatal you are doing the thing you are not supposed to do. A simple post-update hook would suffice to update the views data cache.

jibran’s picture

Issue summary: View changes

Updated IS.

phenaproxima’s picture

+++ b/core/modules/views/views.views.inc
@@ -791,49 +792,51 @@ function core_field_views_data(FieldStorageConfigInterface $field_storage) {
+    if ($target_entity_type instanceof ContentEntityType) {

Should this be using ContentEntityTypeInterface, rather than the concrete class?

Lendude’s picture

#24 yes it should. Blind c/p from the other instance

Status: Needs review » Needs work

The last submitted patch, 25: 2838555-25.patch, failed testing. View results
- codesniffer_fixes.patch Interdiff of automated coding standards fixes only.

Lendude’s picture

Status: Needs work » Needs review

Unrelated fail

Lendude’s picture

Status: Needs review » Needs work

Hmm NW for the update hook

Lendude’s picture

Status: Needs work » Needs review
FileSize
494 bytes
6.85 KB

And here we go with an empty update to clear the Views data.

jibran’s picture

+++ b/core/modules/views/views.views.inc
@@ -792,7 +792,7 @@
+    if ($target_entity_type instanceof ContentEntityTypeInterface) {

This fix is now same as \Drupal\views\EntityViewsData::processViewsDataForEntityReference so this is fine but we could reduce the patch size by using following code:

    if (!$target_entity_type instanceof ContentEntityTypeInterface) {
      continue;
    }
+++ b/core/modules/field/tests/src/Kernel/EntityReference/Views/EntityReferenceRelationshipTest.php
@@ -348,4 +349,18 @@ public function testGroupByWithEmptyRelationships() {
+    $this->assertFalse(isset($views_data['entity_test__field_test_config_entity']['field_test_config_entity']['relationship']));

As this code test shows we are removing the support for config entity relationships. Currently, as soon as you add a relationship with config entity you see the following error:

And view stops showing results. You can still save the view with the error but the view will not show results and generate an error on every page load.

After applying the patch you see

but without any error and view start showing results and there is no error on the page load.

Given above I'd say an empty hook is the best way forward rather than removing broken views relationships from already broken views.

  • catch committed bbe45b8 on 9.1.x
    Issue #2838555 by Lendude, superlolo95, pameeela, jibran, phenaproxima:...
catch’s picture

Status: Reviewed & tested by the community » Fixed

Committed/pushed to 9.1.x, thanks!

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.