The issue I am running into involves the following:

I have a taxonomy vocabulary with several entries. A node can be tagged with any one of these taxonomy terms. In the view I add a filter for "Content: Has taxonomy term" and choose the vocabulary. Now I expose the view to the user and make it into a Group Filter using dropdown.

I make three different options (ex: Patients, Doctors, Nurses) all with several different taxonomy terms selected. So the Doctor option would have "Primary Care Doctor" and "Specialty Doctor" and so on. Likewise there would be different taxonomy terms such as "Critical Patient" or "Permenant Patient" for the Patient option in the dropdown.

The SQL query outputs something like the following:

SELECT node.nid AS nid, node.title AS node_title, 'node' AS field_data_field_people_node_entity_type
FROM 
{node} node
INNER JOIN {taxonomy_index} taxonomy_index ON node.nid = taxonomy_index.nid
WHERE (( (node.status = '1') AND (node.type IN  ('profile_page')) AND (taxonomy_index.tid = '1') ))

What is happening is it is outputting the index of the select tag. If the user were to select the second option in the dropdown (say Doctors) than the taxonomy_index = '2' and if the user selects the third option it is '3'. Instead it should be querying for each possible taxonomy term using the taxonomy terms specified in the Group Filter multiselect.

I haven't gone through all of the View code yet, but I will update this post as I figure out where it is going wrong.

Files: 
CommentFileSizeAuthor
#22 2049603-fixing-tid-in-exposed-group-filter-sql-query-in-views.patch1002 bytesyoufei.sun
#14 views-handler-grouped-filters-use-incorrect-values-2049603-7.patch631 bytesseanmrafferty
FAILED: [[SimpleTest]]: [MySQL] Failed to run tests: tests were executed, but no results were found. View
#12 views-handler-grouped-filters-use-incorrect-values-2049603-6.patch520 bytesseanmrafferty
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch views-handler-grouped-filters-use-incorrect-values-2049603-6.patch. Unable to apply patch. See the log in the details link for more information. View
#9 views-handler-grouped-filters-use-incorrect-values-2049603-5.patch605 bytesseanmrafferty
FAILED: [[SimpleTest]]: [MySQL] 1,624 pass(es), 3 fail(s), and 0 exception(s). View
#4 views-handler-grouped-filters-use-incorrect-values-2049603-4.patch517 bytesseanmrafferty
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch views-handler-grouped-filters-use-incorrect-values-2049603-4.patch. Unable to apply patch. See the log in the details link for more information. View
#1 views-handler-grouped-filters-use-incorrect-values-2049603-1.patch829 bytesseanmrafferty
FAILED: [[SimpleTest]]: [MySQL] 1,624 pass(es), 3 fail(s), and 0 exception(s). View

Comments

seanmrafferty’s picture

Status: Active » Needs review
FileSize
829 bytes
FAILED: [[SimpleTest]]: [MySQL] 1,624 pass(es), 3 fail(s), and 0 exception(s). View

The problem appears to be located in the function called add_filter in views/includes/handlers.inc. The $value for the where clause of the filter comes from $this->handler->value. But if you are using Grouped Filters (and in this case they are taxonomy terms), this value is simply the generic index (i.e. the key) in the array e.g. 1, 2, 3, etc. If you are using Grouped Filters, the $value should be an array of tid's from the corresponding taxonomy terms.

I created a patch to check if Grouped Filters are being used. If so, I get the $value from $this->handler->group_info which contains all the tid's that need to be added to the where clause of the sql query.

I'm not sure if this the correct way to solve this problem, but it seems to work.

Status: Needs review » Needs work
seanmrafferty’s picture

The same tests fail when run without my patch. Not sure how to handle that.

seanmrafferty’s picture

Status: Needs work » Needs review
FileSize
517 bytes
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch views-handler-grouped-filters-use-incorrect-values-2049603-4.patch. Unable to apply patch. See the log in the details link for more information. View

Previous patch was using incorrect test to determine if filters were grouped.

Status: Needs review » Needs work
jiakomo’s picture

seanmrafferty’s picture

Status: Closed (duplicate) » Needs work

Thanks jiakomo. #1810148: Grouped exposed taxonomy term filters do not work does appear be the same the issue. We will try that patch and see if it resolves the issue. While my patch fixes the issue, it doesn't pass automated testing yet. I'm still learning the views code so I can create a proper patch.

seanmrafferty’s picture

Status: Needs work » Closed (duplicate)

Duplicate. #1810148: Grouped exposed taxonomy term filters do not work appears to be a better solution.

seanmrafferty’s picture

Status: Needs work » Closed (duplicate)
FileSize
605 bytes
FAILED: [[SimpleTest]]: [MySQL] 1,624 pass(es), 3 fail(s), and 0 exception(s). View

Unfortunately, it appears we still need this patch for our project. #1810148: Grouped exposed taxonomy term filters do not work alone does not solve the problem we are having. The previous version of this patch failed when being applied to some branches of Views 7.x-3.x.

seanmrafferty’s picture

Status: Closed (duplicate) » Needs review

Status: Needs review » Needs work
seanmrafferty’s picture

FileSize
520 bytes
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch views-handler-grouped-filters-use-incorrect-values-2049603-6.patch. Unable to apply patch. See the log in the details link for more information. View

Updated to ensure right $value is set in all views scenarios.

seanmrafferty’s picture

Status: Needs work » Needs review
seanmrafferty’s picture

FileSize
631 bytes
FAILED: [[SimpleTest]]: [MySQL] Failed to run tests: tests were executed, but no results were found. View

Patch 6 was dependent upon patch 5. Made patch 7 independent.

CvW’s picture

I have a similar problem with a view (in views 7.x-3.7)
The view is as follows:

$view = new view();
$view->name = 'catalogus_test';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Catalogus test';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Catalogus test';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['query']['options']['pure_distinct'] = TRUE;
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = '100';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['pager']['options']['id'] = '0';
$handler->display->display_options['pager']['options']['quantity'] = '9';
$handler->display->display_options['pager']['options']['expose']['items_per_page_label'] = 'Items per pagina';
$handler->display->display_options['pager']['options']['expose']['items_per_page_options_all_label'] = '- Alle -';
$handler->display->display_options['pager']['options']['expose']['offset_label'] = 'Startpunt';
$handler->display->display_options['pager']['options']['tags']['first'] = '« eerste';
$handler->display->display_options['pager']['options']['tags']['previous'] = '‹ vorige';
$handler->display->display_options['pager']['options']['tags']['next'] = 'volgende ›';
$handler->display->display_options['pager']['options']['tags']['last'] = 'laatste »';
$handler->display->display_options['style_plugin'] = 'table';
/* Relationship: Content: Instrumenten (field_instrumenten) */
$handler->display->display_options['relationships']['field_instrumenten_value']['id'] = 'field_instrumenten_value';
$handler->display->display_options['relationships']['field_instrumenten_value']['table'] = 'field_data_field_instrumenten';
$handler->display->display_options['relationships']['field_instrumenten_value']['field'] = 'field_instrumenten_value';
$handler->display->display_options['relationships']['field_instrumenten_value']['delta'] = '-1';
/* Field: Content: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['label'] = 'Titel';
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = FALSE;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = FALSE;
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Content: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'set_bladmuziek' => 'set_bladmuziek',
);
/* Filter criterion: Field collection item: Instrument (field_instrument) */
$handler->display->display_options['filters']['field_instrument_tid']['id'] = 'field_instrument_tid';
$handler->display->display_options['filters']['field_instrument_tid']['table'] = 'field_data_field_instrument';
$handler->display->display_options['filters']['field_instrument_tid']['field'] = 'field_instrument_tid';
$handler->display->display_options['filters']['field_instrument_tid']['relationship'] = 'field_instrumenten_value';
$handler->display->display_options['filters']['field_instrument_tid']['exposed'] = TRUE;
$handler->display->display_options['filters']['field_instrument_tid']['expose']['operator_id'] = 'field_instrument_tid_op';
$handler->display->display_options['filters']['field_instrument_tid']['expose']['label'] = 'Instrument (field_instrument)';
$handler->display->display_options['filters']['field_instrument_tid']['expose']['operator'] = 'field_instrument_tid_op';
$handler->display->display_options['filters']['field_instrument_tid']['expose']['identifier'] = 'field_instrument_tid';
$handler->display->display_options['filters']['field_instrument_tid']['is_grouped'] = TRUE;
$handler->display->display_options['filters']['field_instrument_tid']['group_info']['label'] = 'Instrument (field_instrument)';
$handler->display->display_options['filters']['field_instrument_tid']['group_info']['identifier'] = 'field_instrument_tid';
$handler->display->display_options['filters']['field_instrument_tid']['group_info']['group_items'] = array(
  1 => array(
    'title' => 'blazers',
    'operator' => 'not',
    'value' => array(
      101 => '101',
      103 => '103',
      93 => '93',
      75 => '75',
      96 => '96',
      98 => '98',
      124 => '124',
      76 => '76',
      95 => '95',
      77 => '77',
      100 => '100',
      102 => '102',
      94 => '94',
      78 => '78',
      99 => '99',
      104 => '104',
      79 => '79',
      97 => '97',
      125 => '125',
      31 => '31',
      132 => '132',
      32 => '32',
      33 => '33',
      34 => '34',
      35 => '35',
      106 => '106',
      130 => '130',
      37 => '37',
    ),
  ),
  2 => array(
    'title' => 'strijkers',
    'operator' => 'not',
    'value' => array(
      75 => '75',
      81 => '81',
      90 => '90',
      95 => '95',
      100 => '100',
      82 => '82',
      102 => '102',
      94 => '94',
      91 => '91',
      83 => '83',
      99 => '99',
      92 => '92',
      104 => '104',
      84 => '84',
      97 => '97',
      86 => '86',
      85 => '85',
      87 => '87',
      88 => '88',
      125 => '125',
      31 => '31',
      132 => '132',
      32 => '32',
      33 => '33',
      34 => '34',
      35 => '35',
      106 => '106',
      130 => '130',
      37 => '37',
    ),
  ),
);
$handler->display->display_options['filters']['field_instrument_tid']['type'] = 'select';
$handler->display->display_options['filters']['field_instrument_tid']['vocabulary'] = 'instrumentgroepen';

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'catalogus-test';
$translatables['catalogus_test'] = array(
  t('Master'),
  t('Catalogus test'),
  t('meer'),
  t('Apply'),
  t('Reset'),
  t('Sort by'),
  t('Asc'),
  t('Desc'),
  t('Items per page'),
  t('- All -'),
  t('Offset'),
  t('« first'),
  t('‹ previous'),
  t('next ›'),
  t('last »'),
  t('field collection item from field_instrumenten'),
  t('Title'),
  t('Instrument'),
  t('Page'),
);

The generated query is the same, with and without patch:

SELECT node.title AS node_title, node.nid AS nid, node.language AS node_language
FROM 
{node} node
LEFT JOIN {field_data_field_instrumenten} field_data_field_instrumenten ON node.nid = field_data_field_instrumenten.entity_id AND (field_data_field_instrumenten.entity_type = 'node' AND field_data_field_instrumenten.deleted = '0')
LEFT JOIN {field_collection_item} field_collection_item_field_data_field_instrumenten ON field_data_field_instrumenten.field_instrumenten_value = field_collection_item_field_data_field_instrumenten.item_id
LEFT JOIN {field_data_field_instrument} field_collection_item_field_data_field_instrumenten__field_data_field_instrument 
ON field_collection_item_field_data_field_instrumenten.item_id = field_collection_item_field_data_field_instrumenten__field_data_field_instrument.entity_id 
AND field_collection_item_field_data_field_instrumenten__field_data_field_instrument.field_instrument_tid = '2'
WHERE (( (node.status = '1') AND (node.type IN  ('set_bladmuziek')) 
AND (field_collection_item_field_data_field_instrumenten__field_data_field_instrument.field_instrument_tid IS NULL ) ))
LIMIT 100 OFFSET 0

In the third JOIN the tid still points to the grouped filter id, not to the group of tid's in the filter.

Kiwa’s picture

Tried the patch in #14 with views version 7.x-3.10 and it solved the problem for me.

hunchang’s picture

Applied the patch in 7.x-3.8, it is working, and just upgraded to 7.x-3.10, seems like working properly for me.

Status: Needs review » Needs work
youfei.sun’s picture

Version: 7.x-3.7 » 8.x-3.x-dev
Status: Needs work » Active
Related issues: +#2369119: Problems with a number of views grouped filters

This issue persists in d8 core
8.2.x-dev
Updating issue. And hopefully the fix in d7 can be ported.

Also, a bug in 8.2.x-dev https://www.drupal.org/node/2369119 is preventing this issue to be reproduced. Apply the #145 patch on that thread before trying to reproduce this issue.

youfei.sun’s picture

Project: Views » Drupal core
Version: 8.x-3.x-dev » 8.2.x-dev
Component: exposed filters » views.module
youfei.sun’s picture

I've made a patch based on the d7 work previously, hope this will fix the problem.

youfei.sun’s picture

Status: Active » Needs review
dagmar’s picture

Status: Needs review » Needs work
+++ b/core/modules/views/src/ManyToOneHelper.php
@@ -263,7 +263,11 @@ public function addFilter() {
+    if (isset($options['is_grouped']) && $options['is_grouped'] && isset($this->handler->group_info)) {
...
+    } else {

This doesn't respect Drupal Coding Standars.

Also, if (isset($options['is_grouped']) && $options['is_grouped'] ...could be replaced by if (!empty($options['is_grouped'] && ..

mpdonadio’s picture

Issue tags: +Needs tests, +VDC
+++ b/core/modules/views/src/ManyToOneHelper.php
@@ -263,7 +263,11 @@ public function addFilter() {
+    } else {

Nit, but the `else {` should be on a new line (Drupal coding standards doesn't use cuddled elses).

This needs a test to demonstrate the problem (posted as a test-only) and included in the patch to show that it fixes it.

Lendude’s picture

@youfei.sun thanks for looking into this!

Since #2369119: Problems with a number of views grouped filters only prevents saving the View it should be possible to reproduce and test this when just using the preview and a pre-configured View.

youfei.sun’s picture

Thanks for the code review, currently I am dead busy, looking to get a chance at December to get the essential work done so this can be fixed in core.