NOTE: this is actually a 8.x issue, but there is no 8.x-1.x-dev in the version options yet. Could you add a dev release, please?

Problem/Motivation

When adding multiple fields of the same type as selective filters, the SQL query fails:

SQLSTATE[42S22]: Column not found: 1054 Unknown column taxonomy_term_field_data_node__field_product_category.name_1 in where clause

To replicate this behavior, add two relationships to taxonomy terms first, then add selective filters e.g. using "taxonomy term: name" set to use each one of the relationships. The query uses the views internal name (with digit added) in the query instead of the proper database column name.

Proposed resolution

This happens because the internal filter name is used as real_field of the views query instead of the database column name, which needs to be fixed.

Command icon Show commands

Start within a Git clone of the project using the version control instructions.

Or, if you do not have SSH keys set up on git.drupalcode.org:

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

smk-ka created an issue. See original summary.

smk-ka’s picture

Status: Active » Needs review
FileSize
867 bytes
smk-ka’s picture

Issue summary: View changes
bgilhome’s picture

@smk-ka thanks for the patch. I found that for the repo https://github.com/bmcclure/drupal-views_selective_filters, only the first hunk was necessary. Patch attached to update line numbers from latest 8.x-1.x.

dshields’s picture

Status: Needs review » Reviewed & tested by the community
joelpittet’s picture

Version: 7.x-1.x-dev » 8.x-1.x-dev
joelpittet’s picture

Status: Reviewed & tested by the community » Needs review
+++ b/src/Plugin/views/filter/Selective.php
@@ -47,7 +47,7 @@ class Selective extends InOperator {
+        $this->realField = $this->definition['field'];

Should this just be removed then because this is done in the parent init already?

core/modules/views/src/Plugin/views/HandlerBase.php:124

    if (isset($this->definition['field'])) {
      $this->realField = $this->definition['field'];
    }
Andrew Perriman’s picture

I applied the patch at #4 but still get the SQL error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'node__field_fc_bible.field_fc_bible_' in 'field list':...

The view has no taxonomy relationships but two paragraphs field relationships. The field referenced in the SQL response is a paragraphs field.

Ralkeon’s picture

Hi, I rerolled the patch and tested again. It works on my installation! Thanks!

joelpittet’s picture

Status: Needs review » Postponed (maintainer needs more info)

Looking for a response to #7

jkuan’s picture

Hi @joelpittet in response to #7, I tested out the module (8.x-1.x-dev) with 3 entity reference fields with the line

$this->realField = $this->options['selective_display_field'];

removed and it works well.

Changing this line to what is suggested in the patches above still results in an SQL error so removing the line entirely is the solution that works for my project.

sthomen’s picture

Seconding @jkuan here, just removing the realField line made the problem go away.

bmunslow’s picture

Hi,

I'm hitting this error as well, even with a single selective filter field in my view.

Patch #9 fixes the issue for me.

bmunslow’s picture

Status: Postponed (maintainer needs more info) » Needs review

While #9 works fine... I just reviewed feedback from #7 and fully agree with @joelpittet in that it's better to simply to remove this line, since it is done in the parent init.

+++ b/src/Plugin/views/filter/Selective.php
@@ -47,7 +47,7 @@ class Selective extends InOperator {
+        $this->realField = $this->definition['field'];

This fixes the issue for me as well.

Please see MR with the change.

For any who need the patch link for composer:

https://git.drupalcode.org/project/views_selective_filters/-/merge_requests/1.patch

3CWebDev’s picture

#9 worked and was a lifesaver after spinning wheels for hours troubleshooting the error. Thanks!

jazzper’s picture

Thanks for the patches!

I applied #15 and when this didn't work #9 to the dev version. But for me it's not working well.

I'm working on a travel blog and have a Destination and a Route content type. They share 3 List (text) fields. Activity, Culture and Landscape to filter the content. But:

  1. The filters do not show all used terms. See first screenshot.
  2. There are double entries. See first screenshot.
  3. Wrong term is used. E.g. when I filter on the term Snorkeling the term Caving is used in the URL and in the result. See second screenshot.
  4. example

    http://www.khaosanroad-online.test/search-routes?field_activities_ct__va...Caving&field_landscape_ct__value_selective=All&field_culture_ct__value_selective=All

    Wrong result

    The screenshots are made with patch #9. I am not sure if the last problem occurs with #15 because I didn't fully test it.

4kant’s picture

I agree with #11:

removing the line as suggested by #7 works fine

Thanks!

NWOM’s picture

Status: Needs review » Closed (duplicate)
Related issues: +#3175364: List is incorrect if multiple values present per row

This error was actually fixed already in another issue as well while tackling another problem at the same time. I just updated it.

#3175364: List is incorrect if multiple values present per row