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

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

checker’s picture

Here 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).

infojunkie’s picture

Thanks for the clear reproduction steps. I'll work on this shortly.

infojunkie’s picture

Status: Active » Fixed

Fixed in the latest dev. Please try it (12 hours from now or directly from git) and let me know.

checker’s picture

Status: Fixed » Needs work

I 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)

infojunkie’s picture

The most useful would be to post a simple step-by-step recipe on how to reproduce this. Thanks!

checker’s picture

OK, 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.

checker’s picture

Just to notice. I got also an exception with profile 2 (based on entity module too).

Sborsody’s picture

Notice 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.

Sborsody’s picture

Checker,
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.

checker’s picture

Thanks Sborsody. This is truly an alternative but even more complicated.

aa2007’s picture

Sborsody you're the best! your advice helped me very much. Remove views hacks =)

deggertsen’s picture

I'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).

deggertsen’s picture

I was able to do what I needed with this module. http://drupal.org/project/better_exposed_filters

Maybe that will help somebody else?

dready2011’s picture

Any 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).

gynekolog’s picture

same problem (i use views 3 and drupal commerce)

TechNikh’s picture

I 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

dready2011’s picture

Anyone managed to find a solution? I have been spending days on this, but no luck so far.

sunfire-design’s picture

Status: Needs work » Needs review
FileSize
1.75 KB

Patch for commerce product fields and other fields from relationships.

Its working for me with commerce and some taxonomy reference field.

gynekolog’s picture

#18 works for me, thank you!

kenorb’s picture

Doesn't work for me:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'node_field_data_field_class_ref.field_class_facility_nid' in 'on clause': SELECT DISTINCT taxonomy_term_data_node_taxonomy_term_data.name AS taxonomy_term_data_node_taxonomy_term_data_name FROM {node} node LEFT JOIN {field_data_field_class_facility} field_data_field_class_facility ON node.nid = field_data_field_class_facility.entity_id AND (field_data_field_class_facility.entity_type = :views_join_condition_0 AND field_data_field_class_facility.deleted = :views_join_condition_1) LEFT JOIN {field_data_field_class_ref} field_data_field_class_ref ON node.nid = field_data_field_class_ref.entity_id AND (field_data_field_class_ref.entity_type = :views_join_condition_2 AND field_data_field_class_ref.deleted = :views_join_condition_3) INNER JOIN {node} node_field_data_field_class_ref ON field_data_field_class_ref.field_class_ref_nid = node_field_data_field_class_ref.nid INNER JOIN {node} node_field_data_field_class_facility ON node_field_data_field_class_ref.field_class_facility_nid = node_field_data_field_class_facility.nid LEFT JOIN {taxonomy_term_data} taxonomy_term_data_node ON node_field_data_field_class_facility.term_node_tid = taxonomy_term_data_node.tid INNER JOIN {taxonomy_term_data} taxonomy_term_data_node_taxonomy_term_data ON taxonomy_term_data_node.product_id = taxonomy_term_data_node_taxonomy_term_data.entity_id WHERE (( (node.nid IN (:oids_0, :oids_1, :oids_2, :oids_3)) )); Array ( [:views_join_condition_0] => node [:views_join_condition_1] => 0 [:views_join_condition_2] => node [:views_join_condition_3] => 0 [:oids_0] => 42 [:oids_1] => 12 [:oids_2] => 47 [:oids_3] => 8 ) in _views_filter_selective_query() (line 97 of 
views_hacks/views_filters_selective/views_filters_selective.module).

Filter:
Taxonomy term: Name (selective) (exposed)
which using:
Relationship:
Taxonomy term: Content using Tags

kenorb’s picture

Status: Needs review » Needs work
kenorb’s picture

kenorb’s picture

#18 patch has hard-coded field name, so it's useless without commerce installed.

+      'left_field' => 'product_id',
terry22’s picture

Hi,
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).

gynekolog’s picture

Problem with 18# patch is that when i have entity (commerce product) with status "disable", in selective filters is still displaying.

abuzakaria’s picture

Did anyone find a solution for this yet?

deggertsen’s picture

As 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...

Otto Manninen’s picture

I 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

gynekolog’s picture

@pohjaton
I have finished with the same solution. But thank you for share!

eynie’s picture

The 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.

eynie’s picture

Forgot to attach file - here you go:

EDIT: Seems to work only with relationships that do not use another relationship.

eynie’s picture

OK, now seems working also with relationships using other relationships:

ConradFlashback’s picture

It doesn't work with entity reference relationship :(
Any idea?

Hiller’s picture

Hi 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 ))))

jamesdixon’s picture

Status: Needs work » Needs review

Thanks 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.

vaccinemedia’s picture

Hi 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)

anou’s picture

Hello,
For me patch #34 works fine. And should be applied because for now the official release still throw a PDO exception!

dshields’s picture

This still doesn't work.
Anyone making any progress on this?

david_garcia’s picture

Please, 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.

infojunkie’s picture

Issue summary: View changes
Status: Needs review » Closed (won't fix)

This module has been deprecated in favour of https://drupal.org/project/views_selective_filters. Please test there and reopen if necessary.