I have a view there is a relation from node type to product type. If i create a selective filter from a related field I get always an error:
For example with drupal commerce products entity in relation to nodes:
PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_data_field_term.field_term_value' in 'field list': SELECT DISTINCT field_data_field_term.field_term_value AS field_term_value, node.nid AS nid FROM {node} node WHERE (( (node.nid IN (:oids_0, :oids_1, :oids_2)) )); Array ( [:oids_0] => 3 [:oids_1] => 2 [:oids_2] => 1 ) in _views_filter_selective_query() (line 76 of /modules/public/basic/views_hacks/views_filters_selective/views_filters_selective.module).
I will make a better example in the next week. Module version is from 2012-May-11
Comments
Comment #1
checker CreditAttribution: checker commentedHere is an example with nodes and comments. There is a relationship "Comment: Comments of the node" and a filter "(Comments) Comment: Author (selective) (exposed)".
Exported view is attached.
PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'comment.name' in 'field list': SELECT DISTINCT comment.name AS name, node.nid AS nid FROM {node} node WHERE (( (node.nid IN (:oids_0, :oids_1, :oids_2)) )); Array ( [:oids_0] => 3 [:oids_1] => 2 [:oids_2] => 1 ) in _views_filter_selective_query() (line 76 of /views_hacks/views_filters_selective/views_filters_selective.module).
Comment #2
infojunkie CreditAttribution: infojunkie commentedThanks for the clear reproduction steps. I'll work on this shortly.
Comment #3
infojunkie CreditAttribution: infojunkie commentedFixed in the latest dev. Please try it (12 hours from now or directly from git) and let me know.
Comment #4
checker CreditAttribution: checker commentedI have tested the new version (2012-May-18).
Now it is working with core entities but it is still not working with entity API module.
At the moment i put here the sql error message. Please tell me how I can help you with more information or debugging. I guess it would not help if i drop here a view with non core stuff.
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= commerce_product_field_data_field_produkt.product_id WHERE (( (node.nid IN ('3' at line 1: SELECT DISTINCT commerce_product_field_data_field_produkt.sku AS commerce_product_field_data_field_produkt_sku, node.nid AS nid FROM {node} node INNER JOIN {commerce_product} commerce_product_field_data_field_produkt ON .field_produkt_product_id = commerce_product_field_data_field_produkt.product_id WHERE (( (node.nid IN (:oids_0, :oids_1, :oids_2)) )); Array ( [:oids_0] => 3 [:oids_1] => 2 [:oids_2] => 1 ) in _views_filter_selective_query() (line 81 of /views_hacks/views_filters_selective/views_filters_selective.module).
A node entity type view and a relation to commerce product entity type.
Filter criteria
- Content: Published (Yes)
- (Product) Commerce Product: SKU (selective) (exposed)
Relationships
-Content: Product (tested required and not required)
Sort criteria
Content: Post date (desc)
Comment #5
infojunkie CreditAttribution: infojunkie commentedThe most useful would be to post a simple step-by-step recipe on how to reproduce this. Thanks!
Comment #6
checker CreditAttribution: checker commentedOK, here is an easy to reproduce way.
1) You need the following modules:
- commerce http://drupal.org/project/commerce
- entity http://drupal.org/project/entity
- rules http://drupal.org/project/rules
- addressfield http://drupal.org/project/addressfiel
2) Enable these modules:
- product reference
- product UI
3) Add a default product reference field on node type page with name "field_product" (/admin/structure/types/manage/page/fields)
4) Create a product (/admin/commerce/products/add/product)
5) Create a node (type page) and select the created product in the product reference field (/node/add/page)
6) Import my test view
7) open the view (/selected-test)
Then it throws an exception:
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= commerce_product_field_data_field_product.product_id WHERE (( (node.nid IN ('3' at line 1: SELECT DISTINCT commerce_product_field_data_field_product.product_id AS commerce_product_field_data_field_product_product_id, node.nid AS nid FROM {node} node INNER JOIN {commerce_product} commerce_product_field_data_field_product ON .field_product_product_id = commerce_product_field_data_field_product.product_id WHERE (( (node.nid IN (:oids_0)) )); Array ( [:oids_0] => 3 ) in _views_filter_selective_query() (row 81 views_hacks/views_filters_selective/views_filters_selective.module).
Hope this informtion can help you to reproduce this error.
Comment #7
checker CreditAttribution: checker commentedJust to notice. I got also an exception with profile 2 (based on entity module too).
Comment #8
Sborsody CreditAttribution: Sborsody commentedNotice that a table name is missing!
commerce_product_field_data_field_produkt ON .field_produkt_product_id = commerce_product_field_data_field_produkt.product_id WHERE
I'm having the same thing with 7.x-1.0-alpha1 just with taxonomy terms. The table with that column is not included in an inner join.
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= taxonomy_term_data_field_data_field_org_ages.tid
WHERE (( (users.uid IN ('30',' at line 1: SELECT DISTINCT taxonomy_term_data_field_data_field_org_ages.name AS taxonomy_term_data_field_data_field_org_ages_name, users.uid AS uid
FROM
{users} users
INNER JOIN {taxonomy_term_data} taxonomy_term_data_field_data_field_org_ages ON .field_org_ages_tid = taxonomy_term_data_field_data_field_org_ages.tid
WHERE (( (users.uid IN (:oids_0, :oids_1, :oids_2, :oids_3, :oids_4, :oids_5, :oids_6, :oids_7)) )); Array
(
[:oids_0] => 30
[:oids_1] => 28
[:oids_2] => 32
[:oids_3] => 33
[:oids_4] => 34
[:oids_5] => 35
[:oids_6] => 31
[:oids_7] => 29
)
I'm not using Commerce, Profile2, or Entity API. It's vanilla Drupal users with a term reference field added to a single non-hierarchical vocabulary.
Comment #9
Sborsody CreditAttribution: Sborsody commentedChecker,
I was able to achieve what I was trying to do with selective filters by using Search API, Facet API, Search facets, Search views, and Facet API Bonus. (see http://envisioninteractive.com/drupal/drupal-7-views-with-faceted-filter...)
The module facetapi_bonus has an option to not display the filters that do not narrow down the results. That imitates selective filters.
Comment #10
checker CreditAttribution: checker commentedThanks Sborsody. This is truly an alternative but even more complicated.
Comment #11
aa2007 CreditAttribution: aa2007 commentedSborsody you're the best! your advice helped me very much. Remove views hacks =)
Comment #12
deggertsen CreditAttribution: deggertsen commentedI'm having the same problem with product entities. Basically the exact same thing as is explained in #6.
Unfortunately, I have not been able to get Search API to work either as explained in #9 due to the entity relationships (I think).
Comment #13
deggertsen CreditAttribution: deggertsen commentedI was able to do what I needed with this module. http://drupal.org/project/better_exposed_filters
Maybe that will help somebody else?
Comment #14
dready2011 CreditAttribution: dready2011 commentedAny news on this? I am having exactly the same problem as described in #8. I also have the table name missing in the query. My setup is the same as #4 (using commerce).
Comment #15
gynekolog CreditAttribution: gynekolog commentedsame problem (i use views 3 and drupal commerce)
Comment #16
TechNikh CreditAttribution: TechNikh commentedI am having the same issue(missing table name). I am not using any commerce modules. I field is a term reference field within another vocabulary
Comment #17
dready2011 CreditAttribution: dready2011 commentedAnyone managed to find a solution? I have been spending days on this, but no luck so far.
Comment #18
sunfire-design CreditAttribution: sunfire-design commentedPatch for commerce product fields and other fields from relationships.
Its working for me with commerce and some taxonomy reference field.
Comment #19
gynekolog CreditAttribution: gynekolog commented#18 works for me, thank you!
Comment #20
kenorb CreditAttribution: kenorb commentedDoesn't work for me:
Filter:
Taxonomy term: Name (selective) (exposed)
which using:
Relationship:
Taxonomy term: Content using Tags
Comment #21
kenorb CreditAttribution: kenorb commentedComment #22
kenorb CreditAttribution: kenorb commentedActually it doesn't work for me at all:
#1204240: views filters selective seems not to work with current views-3.x-dev
Comment #23
kenorb CreditAttribution: kenorb commented#18 patch has hard-coded field name, so it's useless without commerce installed.
Comment #24
terry22 CreditAttribution: terry22 commentedHi,
Did anybody found a way to make it work with other modules than ecommerce?
like users, profils,etc.
Thank you for your help.
I get this errors with profile2
PDOException : SQLSTATE[42S22]: Column not found: 1054 Unknown column 'profile_users.field_specialite_tid' in 'field list': SELECT DISTINCT profile_users.field_specialite_tid AS profile_users_field_specialite_tid FROM {users} users INNER JOIN {profile} profile_users ON users.uid = profile_users.uid WHERE (( (users.uid IN (:oids_0, :oids_1, :oids_2, :oids_3, :oids_4, :oids_5, :oids_6, :oids_7, :oids_8, :oids_9, :oids_10, :oids_11, :oids_12, :oids_13, :oids_14, :oids_15, :oids_16, :oids_17, :oids_18, :oids_19, :oids_20, :oids_21, :oids_22, :oids_23, :oids_24, :oids_25, :oids_26, :oids_27, :oids_28, :oids_29, :oids_30, :oids_31, :oids_32, :oids_33, :oids_34, :oids_35, :oids_36, :oids_37, :oids_38, :oids_39, :oids_40, :oids_41, :oids_42, :oids_43, :oids_44, :oids_45, :oids_46, :oids_47, :oids_48, :oids_49, :oids_50, :oids_51, :oids_52, :oids_53, :oids_54, :oids_55, :oids_56, :oids_57, :oids_58, :oids_59, :oids_60, :oids_61, :oids_62, :oids_63, :oids_64, :oids_65, :oids_66, :oids_67, :oids_68, :oids_69, :oids_70, :oids_71, :oids_72, :oids_73, :oids_74, :oids_75, :oids_76, :oids_77, :oids_78, :oids_79, :oids_80, :oids_81, :oids_82, :oids_83, :oids_84, :oids_85, :oids_86, :oids_87, :oids_88, :oids_89, :oids_90, :oids_91, :oids_92, :oids_93, :oids_94, :oids_95, :oids_96, :oids_97, :oids_98, :oids_99, :oids_100, :oids_101, :oids_102, :oids_103, :oids_104, :oids_105, :oids_106, :oids_107)) )); Array ( [:oids_0] => 91 [:oids_1] => 39 [:oids_2] => 40 [:oids_3] => 48 [:oids_4] => 50 [:oids_5] => 41 [:oids_6] => 42 [:oids_7] => 121 [:oids_8] => 73 [:oids_9] => 126 [:oids_10] => 96 [:oids_11] => 127 [:oids_12] => 70 [:oids_13] => 122 [:oids_14] => 97 [:oids_15] => 128 [:oids_16] => 123 [:oids_17] => 36 [:oids_18] => 129 [:oids_19] => 74 [:oids_20] => 51 [:oids_21] => 78 [:oids_22] => 52 [:oids_23] => 130 [:oids_24] => 144 [:oids_25] => 53 [:oids_26] => 82 [:oids_27] => 99 [:oids_28] => 54 [:oids_29] => 100 [:oids_30] => 43 [:oids_31] => 63 [:oids_32] => 38 [:oids_33] => 55 [:oids_34] => 147 [:oids_35] => 102 [:oids_36] => 103 [:oids_37] => 131 [:oids_38] => 132 [:oids_39] => 133 [:oids_40] => 56 [:oids_41] => 83 [:oids_42] => 124 [:oids_43] => 88 [:oids_44] => 89 [:oids_45] => 77 [:oids_46] => 105 [:oids_47] => 37 [:oids_48] => 84 [:oids_49] => 107 [:oids_50] => 148 [:oids_51] => 72 [:oids_52] => 68 [:oids_53] => 134 [:oids_54] => 85 [:oids_55] => 57 [:oids_56] => 108 [:oids_57] => 58 [:oids_58] => 44 [:oids_59] => 90 [:oids_60] => 59 [:oids_61] => 109 [:oids_62] => 60 [:oids_63] => 135 [:oids_64] => 136 [:oids_65] => 110 [:oids_66] => 111 [:oids_67] => 113 [:oids_68] => 112 [:oids_69] => 137 [:oids_70] => 76 [:oids_71] => 114 [:oids_72] => 61 [:oids_73] => 145 [:oids_74] => 149 [:oids_75] => 75 [:oids_76] => 138 [:oids_77] => 62 [:oids_78] => 115 [:oids_79] => 116 [:oids_80] => 125 [:oids_81] => 139 [:oids_82] => 117 [:oids_83] => 69 [:oids_84] => 64 [:oids_85] => 92 [:oids_86] => 86 [:oids_87] => 71 [:oids_88] => 79 [:oids_89] => 118 [:oids_90] => 140 [:oids_91] => 141 [:oids_92] => 65 [:oids_93] => 66 [:oids_94] => 150 [:oids_95] => 146 [:oids_96] => 142 [:oids_97] => 80 [:oids_98] => 87 [:oids_99] => 45 [:oids_100] => 46 [:oids_101] => 119 [:oids_102] => 67 [:oids_103] => 151 [:oids_104] => 120 [:oids_105] => 81 [:oids_106] => 143 [:oids_107] => 47 ) dans _views_filter_selective_query() (ligne 81 dans /home/xxx/public_html/sites/all/modules/views_hacks/views_filters_selective/views_filters_selective.module).
Comment #25
gynekolog CreditAttribution: gynekolog commentedProblem with 18# patch is that when i have entity (commerce product) with status "disable", in selective filters is still displaying.
Comment #26
abuzakaria CreditAttribution: abuzakaria commentedDid anyone find a solution for this yet?
Comment #27
deggertsen CreditAttribution: deggertsen commentedAs noted in #23, patch #18 is pretty much useless unless you happen to be using the exact field that @sunfire-design is using. I haven't been able to think of a way to fix this yet and unless my client pushes me to get it done I will probably just forget it...
Comment #28
Otto Manninen CreditAttribution: Otto Manninen commentedI fixed this (entity (commerce product) with status "disable" showing) temporarily by adding this to the $join->definition array in views_filters_selective.module
// Only get enabled products
'extra' => 'commerce_product_field_data_field_upc_codes.status = 1',
So the whole array looks like this
$join->definition = array(
'table' => $table_name_field,
'field' => 'entity_id',
'left_table' => $left_table,
'left_field' => 'product_id',
'type' => 'INNER',
// Only get enabled products
'extra' => 'commerce_product_field_data_field_upc_codes.status = 1',
);
You should change commerce_product_field_data_field_upc_codes part to your needs
Comment #29
gynekolog CreditAttribution: gynekolog commented@pohjaton
I have finished with the same solution. But thank you for share!
Comment #30
eynie CreditAttribution: eynie commentedThe following solution worked for me. I'm aware, that it may not fix the problem in all circumstances (does it need to be done in line 120 too?), but I think that it will point somebody to the right direction.
Comment #31
eynie CreditAttribution: eynie commentedForgot to attach file - here you go:
EDIT: Seems to work only with relationships that do not use another relationship.
Comment #32
eynie CreditAttribution: eynie commentedOK, now seems working also with relationships using other relationships:
Comment #33
ConradFlashback CreditAttribution: ConradFlashback commentedIt doesn't work with entity reference relationship :(
Any idea?
Comment #34
Hiller CreditAttribution: Hiller commentedHi below is a patch that fixes almost all problems with relations. Now selective filters can not only use forward relations but also reverse (content referencing another one throught referenced relation)
NB. Patch was created against dev version not a release one. Though i think you know how to apply it
upd. looks like i've posted a patch that also fixes a 'Actual values will be available at run-time' message beeing shown for the first/single selective filter on a page sorry ))))
Comment #35
jamesdixon CreditAttribution: jamesdixon commentedThanks Hiller #34 worked beautifully. I had a view that referenced entities with relationships a few levels deep and this patch got the views selective filters operating smoothly.
Comment #36
vaccinemedia CreditAttribution: vaccinemedia commentedHi Hiller. I applied the patch but now the settings for Views Better Exposed Filters don't appear when clicking on the link to edit them and reordering the filters causes a white page to display with all of the variables for the view on (see attached)
Comment #37
anouHello,
For me patch #34 works fine. And should be applied because for now the official release still throw a PDO exception!
Comment #38
dshields CreditAttribution: dshields commentedThis still doesn't work.
Anyone making any progress on this?
Comment #39
david_garcia CreditAttribution: david_garcia commentedPlease, help review this SandBox module:
https://drupal.org/sandbox/david_garcia_garcia/2162097
So we can decide if it can properly replace current module.
Snapshot of current release:
http://drupalcode.org/sandbox/david_garcia_garcia/2162097.git/snapshot/H...
Thank you.
Comment #40
infojunkie CreditAttribution: infojunkie commentedThis module has been deprecated in favour of https://drupal.org/project/views_selective_filters. Please test there and reopen if necessary.