I'm getting the below, in a View of a custom entity with a entity reference field with a taxonomy term (field definition below).
The strange think is that this error doesn't appear for the admin user but appears for a user with less privileges. Testing I found that the permission which causes the views works or not is "Adminster Tac Lite"

This is the error:

Drupal\Core\Database\DatabaseExceptionWrapper: Exception in Report[report]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'stat_entity.tid' in 'where clause': SELECT stat_entity.front_user AS stat_entity_front_user, stat_entity.metric AS stat_entity_metric, stat_entity.id AS id, taxonomy_term_field_data_stat_entity.tid AS taxonomy_term_field_data_stat_entity_tid FROM {stat_entity} stat_entity INNER JOIN {taxonomy_term_field_data} taxonomy_term_field_data_stat_entity ON stat_entity.metric = taxonomy_term_field_data_stat_entity.tid LEFT OUTER JOIN {taxonomy_term_field_data} tac_td ON stat_entity.tid = tac_td.tid WHERE ( (stat_entity.tid IN (:db_condition_placeholder_0, :db_condition_placeholder_1, :db_condition_placeholder_2, :db_condition_placeholder_3, :db_condition_placeholder_4)) OR (tac_td.vid NOT IN (:db_condition_placeholder_5)) ) ORDER BY stat_entity_front_user ASC, stat_entity_metric ASC LIMIT 51 OFFSET 0; Array ( [:db_condition_placeholder_0] => 0 [:db_condition_placeholder_1] => 0 [:db_condition_placeholder_2] => 1 [:db_condition_placeholder_3] => 2 [:db_condition_placeholder_4] => 3 [:db_condition_placeholder_5] => status ) in Drupal\views\Plugin\views\query\Sql->execute() (line 1488 of core/modules/views/src/Plugin/views/query/Sql.php).

This is the field definition:

    $fields['metric'] = BaseFieldDefinition::create('entity_reference')
      ->setLabel(t('Metric'))
      ->setDescription(t('Metric taxonomy that is the Log Type'))
      ->setRevisionable(TRUE)
      ->setSetting('target_type', 'taxonomy_term')
     ->setSetting('handler', 'default:taxonomy_term')
      ->setSetting('handler_settings',  array(
          'target_bundles' => array(
            'logs_type' => 'logs_type',
          )
        )
      )
      ->setDisplayOptions('view', array(
        'label' => 'above',
        'type' => 'entity_reference_label',
        'weight' => 4,
      ))
      ->setDisplayOptions('form', array(
        'weight' => 4,
        'type' => 'options_select',
      ))
      ->setRequired(true)
      ->setTranslatable(false)
      ->setDisplayConfigurable('form', TRUE)
      ->setDisplayConfigurable('view', TRUE);
CommentFileSizeAuthor
#6 primary_table-2850750-6.patch507 bytesTomazetti
#4 primary_table-2850750-4.patch449 bytesTomazetti
views.view_.report.yml24 KBferriol
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

ferriol created an issue. See original summary.

cocorodeo’s picture

cocorodeo CreditAttribution: cocorodeo commented

Hello,

I confirm that I'm having the exact same issue with the same configuration : a custom view with etity reference field to taxonomy_term.
And the problem occurs only for anonymous user (in my case only admin can administer tac_lite) ...

Is this issue fixed in dev version ?

[UPDATE]

I've installed the dev version and the problem still occurs ... the generated sql request is wrong when the view has entity reference to taxonomy term ...

tac_lite adds a INNER JOIN to a non existing table ex :

"taxonomy_term_field_data_node__field_theme" where "_field_theme" is actually a node field table ex "node_field_theme" .

I'm still investigating ... :(

[UPDATE]

The table of the join is not in cause, this is the join column criteria which is wrong.
Ex request generated : 

SELECT
  node_field_data.title                          AS node_field_data_title,
  node_field_data.nid                            AS nid,
  taxonomy_term_field_data_node__field_theme.tid AS taxonomy_term_field_data_node__field_theme_tid
FROM
  node_field_data node_field_data
  LEFT JOIN node__field_theme node__field_theme ON node_field_data.nid = node__field_theme.entity_id AND
                                                   (node__field_theme.deleted = :views_join_condition_0 AND
                                                    node__field_theme.langcode = node_field_data.langcode)
  INNER JOIN taxonomy_term_field_data taxonomy_term_field_data_node__field_theme
    ON node__field_theme.field_theme_target_id = taxonomy_term_field_data_node__field_theme.tid
  LEFT OUTER JOIN taxonomy_term_field_data tac_td ON node_field_data.tid = tac_td.tid
WHERE (((node__field_theme.field_theme_target_id = :node__field_theme_field_theme_target_id)) AND
       (node_field_data.type IN (:db_condition_placeholder_2))) AND (EXISTS(SELECT na.nid AS nid
                                                                            FROM
                                                                              node_access na
                                                                            WHERE (((gid IN
                                                                                     (:db_condition_placeholder_3)) AND
                                                                                    (realm =
                                                                                     :db_condition_placeholder_4)) OR (
                                                                                     (gid IN
                                                                                      (:db_condition_placeholder_5, :db_condition_placeholder_6, :db_condition_placeholder_7, :db_condition_placeholder_8, :db_condition_placeholder_9, :db_condition_placeholder_10, :db_condition_placeholder_11))
                                                                                     AND (realm =
                                                                                          :db_condition_placeholder_12)))
                                                                                  AND (na.grant_view >=
                                                                                       :db_condition_placeholder_13) AND
                                                                                  ((node_field_data.nid = na.nid)))) AND
      ((node_field_data.tid IN (:db_condition_placeholder_14)) OR (tac_td.vid NOT IN (:db_condition_placeholder_15)))
ORDER BY node_field_data_title ASC NULLS FIRST;

The column node_field_data.tid in the LEFT OUTER JOIN is wrong, it does not exist.

The column join should be node__field_theme.field_theme_target_id

In file tac_lite.module, at line 246, there is a comment : 

// HELP: What is the proper way to find the alias of the primary table here?
    $primary_table = '';
    $t = $query->getTables();
    foreach ($t as $info) {
      if (!$info['join type']) {
        $primary_table = $info['alias'];
      }
    }

I think the question is good ;) and the test may not be sufficient ... This is where the LEFT OUTER JOIN is added I'm still on th case ...

cocorodeo’s picture

cocorodeo CreditAttribution: cocorodeo commented

I think my previous comments are related to issue #1442418

Tomazetti’s picture

Tomazetti CreditAttribution: Tomazetti commented
Status: Active » Needs review
FileSize
primary_table-2850750-4.patch449 bytes

I made a patch to try to fix this problem.

Tomazetti’s picture

Tomazetti CreditAttribution: Tomazetti commented
Related issues: +#2912334: HELP: What is the proper way to find the alias of the primary table here?
Tomazetti’s picture

Tomazetti CreditAttribution: Tomazetti commented
FileSize
primary_table-2850750-6.patch507 bytes

Forgot to add taxonomy_term_data in the previous patch.

Tomazetti’s picture

Tomazetti CreditAttribution: Tomazetti commented
mngates’s picture

mngates CreditAttribution: mngates commented

Thank you, @Tomazetti. I applied your patch in #6 and it solved white-screening and SQL errors. Works great for me.

abu-zakham’s picture

abu-zakham CreditAttribution: abu-zakham at Vardot commented
Status: Needs review » Reviewed & tested by the community

Thank you, @Tomazetti.

abu-zakham’s picture

abu-zakham CreditAttribution: abu-zakham at Vardot commented
Version: 8.x-1.1 » 8.x-1.x-dev
stevecowie’s picture

stevecowie CreditAttribution: stevecowie commented

I've also hit same problem and confirm that @Tomazetti patch at #6 fixes it.

slefevre@ccad.edu’s picture

slefevre@ccad.edu CreditAttribution: slefevre@ccad.edu commented

RTBC, we've been running patch #6 for months.

nicholasThompson’s picture

nicholasThompson
Primary language English
CreditAttribution: nicholasThompson at Full Fat Things commented

Confirmed - this fixes the error for us too

  • mukila committed 87b00be on 8.x-1.x 
    Issue #2850750 by Tomazetti, ferriol: SQL Column not found error when...
mukila’s picture

mukila CreditAttribution: mukila commented
Status: Reviewed & tested by the community » Fixed

Commited in dev branch

Status: Fixed » Closed (fixed)

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