SQL error when create a views filter with negative detph

Created on 24 November 2022, about 2 years ago

Updated 16 October 2024, 2 months ago

Problem/Motivation

I have create a view with a negative depth and I get a SQL error.

Exception in pruebas[pruebas]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tn.entity_id' in 'on clause': SELECT "node_field_data"."created" AS "node_field_data_created", "node_field_data"."nid" AS "nid" FROM "node_field_data" "node_field_data" WHERE ("node_field_data"."status" = :db_condition_placeholder_0) AND ("node_field_data"."nid" IN (SELECT "tn"."nid" AS "nid" FROM "taxonomy_index" "tn" LEFT OUTER JOIN "taxonomy_term__parent" "tp1" ON tn.entity_id = tp1.parent_target_id LEFT OUTER JOIN "taxonomy_term__parent" "tp2" ON tp1.entity_id = tp2.parent_target_id LEFT OUTER JOIN "taxonomy_term__parent" "tp3" ON tp2.entity_id = tp3.parent_target_id LEFT OUTER JOIN "taxonomy_term__parent" "tp4" ON tp3.entity_id = tp4.parent_target_id WHERE ("tn"."tid" = :db_condition_placeholder_1) OR ("tp1"."entity_id" = :db_condition_placeholder_2) OR ("tp2"."entity_id" = :db_condition_placeholder_3) OR ("tp3"."entity_id" = :db_condition_placeholder_4) OR ("tp4"."entity_id" = :db_condition_placeholder_5))) ORDER BY "node_field_data_created" DESC LIMIT 11 OFFSET 0; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => 27 [:db_condition_placeholder_2] => 27 [:db_condition_placeholder_3] => 27 [:db_condition_placeholder_4] => 27 [:db_condition_placeholder_5] => 27 ) en main() (linea 19 de /var/www/html/web/index.php).

Steps to reproduce

Create a view.
Add a exposed filter criteria "Content: Has taxonomy terms (with depth)" with a negative value.
Use the exposed filter.

Proposed resolution

The problem is in ShsTaxonomyIndexTidDepth:: query, line 154 to 160.

This code works great in a local instalation:

    elseif ($this->options['depth'] < 0) {

      foreach (range(1, abs($this->options['depth'])) as $key => $count) {

        if($key === 0){

          $subquery->leftJoin('taxonomy_term__parent', "tp$count", "tn.tid = tp$count.parent_target_id");

        }

        else {

          $subquery->leftJoin('taxonomy_term__parent', "tp$count", "$last.entity_id = tp$count.parent_target_id");

        }

        $where->condition("tp$count.entity_id", $this->value, $operator);

        $last = "tp$count";

      }

    }

Remaining tasks

User interface changes

API changes

Data model changes

🐛 Bug report

Status

RTBC

Version

2.0

Component

Code

Created by

🇪🇸Spain oskar_calvo

Live updates comments and jobs are added and updated live.

Incomplete comments

Merge Requests

!8SQL error when create a views filter with negative detph
Open
🇪🇸Spain oskar_calvo
updated about 2 months ago

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

Merge request !8Issue #3323584: SQL error when create a views filter with negative detph → (Open) created by oskar_calvo
Comment 2 months ago →
🇫🇮Finland heikkiy Oulu
We encountered this same issue in a view that was not actually using the view filter but was still encountering the error with a negative depth.

The patch worked so marking this as RTBC.
Comment about 2 months ago →
🇨🇦Canada joseph.olstad
Thanks for the review, checking patch
Comment about 2 months ago →
🇨🇦Canada joseph.olstad
Looks good, should go in soon!
Comment about 2 months ago →
🇫🇮Finland heikkiy Oulu
I wonder if it would be a separate issue that the view filter plugin seemed to affect also a view that was not using the Simple hierarchical select selection type but the normal Dropdown? I am not that familiar how the view filters in the core work in the backend but it seems weird that the code execution goes this far to get a SQL error when the selection type is not used.

contrib.social Blog FAQ Discussions

Production build 0.71.5 2024