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.
Comment | File | Size | Author |
---|---|---|---|
#17 | Wrong result.png | 172.67 KB | jazzper |
#17 | Double entries Not showing all terms.png | 180.59 KB | jazzper |
#9 | views_selective_filters-sql_error-2804485-9-8.patch | 484 bytes | Ralkeon |
#4 | 2804485-views_selective_filters-sql_error-4.patch | 504 bytes | bgilhome |
#2 | 2804485.diff | 867 bytes | smk-ka |
Issue fork views_selective_filters-2804485
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:
Comments
Comment #2
smk-ka CreditAttribution: smk-ka commentedComment #3
smk-ka CreditAttribution: smk-ka commentedComment #4
bgilhome CreditAttribution: bgilhome commented@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.
Comment #5
dshields CreditAttribution: dshields commentedComment #6
joelpittetComment #7
joelpittetShould this just be removed then because this is done in the parent init already?
core/modules/views/src/Plugin/views/HandlerBase.php:124
Comment #8
Andrew Perriman CreditAttribution: Andrew Perriman commentedI 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.
Comment #9
Ralkeon CreditAttribution: Ralkeon commentedHi, I rerolled the patch and tested again. It works on my installation! Thanks!
Comment #10
joelpittetLooking for a response to #7
Comment #11
jkuan CreditAttribution: jkuan commentedHi @joelpittet in response to #7, I tested out the module (8.x-1.x-dev) with 3 entity reference fields with the line
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.
Comment #12
sthomen CreditAttribution: sthomen commentedSeconding @jkuan here, just removing the realField line made the problem go away.
Comment #13
bmunslow CreditAttribution: bmunslow commentedHi,
I'm hitting this error as well, even with a single selective filter field in my view.
Patch #9 fixes the issue for me.
Comment #15
bmunslow CreditAttribution: bmunslow commentedWhile #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.
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
Comment #16
3CWebDev CreditAttribution: 3CWebDev commented#9 worked and was a lifesaver after spinning wheels for hours troubleshooting the error. Thanks!
Comment #17
jazzper CreditAttribution: jazzper commentedThanks 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:
http://www.khaosanroad-online.test/search-routes?field_activities_ct__va...Caving&field_landscape_ct__value_selective=All&field_culture_ct__value_selective=All
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.
Comment #18
4kant CreditAttribution: 4kant commentedI agree with #11:
removing the line as suggested by #7 works fine
Thanks!
Comment #19
NWOM CreditAttribution: NWOM commentedThis 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