Hi.
English is not my mother language so I apologize if my message is slightly confused.
I'm using views in version 7.x-3.7 and Drupal in version 7.23.
I have an integer field of the type "list of value".
Basically what shows bellow:
1|One
2|Two
3|Three
I'm trying to use this field to filter and sort one view.
The filter should be: field <> 1 (different of one).
Of course there is not the option "<>" so I tried to use "none of" and selected the line of the value "1" on the list.
Using this option my view is bugged. The filter criteria and the sort is broken.
See below.
Exported view:
$view = new view();
$view->name = 'noticias_em_destaque';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Notícias em destaque';
$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'] = 'Notícias em destaque';
$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['exposed_form']['type'] = 'basic';
$handler->display->display_options['exposed_form']['options']['reset_button_label'] = 'Restaurar';
$handler->display->display_options['pager']['type'] = 'none';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Campo: Conteúdo: Tipo */
$handler->display->display_options['fields']['type']['id'] = 'type';
$handler->display->display_options['fields']['type']['table'] = 'node';
$handler->display->display_options['fields']['type']['field'] = 'type';
$handler->display->display_options['fields']['type']['label'] = '';
$handler->display->display_options['fields']['type']['exclude'] = TRUE;
$handler->display->display_options['fields']['type']['element_label_colon'] = FALSE;
/* Campo: Conteúdo: Título */
$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'] = '';
$handler->display->display_options['fields']['title']['exclude'] = TRUE;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = FALSE;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = FALSE;
$handler->display->display_options['fields']['title']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['title']['link_to_node'] = FALSE;
/* Campo: Path do conteúdo (link interno) */
$handler->display->display_options['fields']['path']['id'] = 'path';
$handler->display->display_options['fields']['path']['table'] = 'node';
$handler->display->display_options['fields']['path']['field'] = 'path';
$handler->display->display_options['fields']['path']['ui_name'] = 'Path do conteúdo (link interno)';
$handler->display->display_options['fields']['path']['label'] = '';
$handler->display->display_options['fields']['path']['exclude'] = TRUE;
$handler->display->display_options['fields']['path']['element_label_colon'] = FALSE;
/* Campo: Link externo */
$handler->display->display_options['fields']['field_notext_link']['id'] = 'field_notext_link';
$handler->display->display_options['fields']['field_notext_link']['table'] = 'field_data_field_notext_link';
$handler->display->display_options['fields']['field_notext_link']['field'] = 'field_notext_link';
$handler->display->display_options['fields']['field_notext_link']['ui_name'] = 'Link externo';
$handler->display->display_options['fields']['field_notext_link']['label'] = '';
$handler->display->display_options['fields']['field_notext_link']['exclude'] = TRUE;
$handler->display->display_options['fields']['field_notext_link']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_notext_link']['click_sort_column'] = 'url';
$handler->display->display_options['fields']['field_notext_link']['type'] = 'link_plain';
/* Campo: Título e link - notícia interna */
$handler->display->display_options['fields']['views_conditional']['id'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional']['table'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional']['field'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional']['ui_name'] = 'Título e link - notícia interna';
$handler->display->display_options['fields']['views_conditional']['label'] = '';
$handler->display->display_options['fields']['views_conditional']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['views_conditional']['if'] = 'type';
$handler->display->display_options['fields']['views_conditional']['condition'] = '1';
$handler->display->display_options['fields']['views_conditional']['equalto'] = 'Notícias';
$handler->display->display_options['fields']['views_conditional']['then'] = '[title]';
/* Campo: Título e link - notícia externa */
$handler->display->display_options['fields']['views_conditional_1']['id'] = 'views_conditional_1';
$handler->display->display_options['fields']['views_conditional_1']['table'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional_1']['field'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional_1']['ui_name'] = 'Título e link - notícia externa';
$handler->display->display_options['fields']['views_conditional_1']['label'] = '';
$handler->display->display_options['fields']['views_conditional_1']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['views_conditional_1']['if'] = 'type';
$handler->display->display_options['fields']['views_conditional_1']['condition'] = '2';
$handler->display->display_options['fields']['views_conditional_1']['equalto'] = 'Notícias';
$handler->display->display_options['fields']['views_conditional_1']['then'] = '[title]';
/* Campo: Conteúdo: Posição da notícia */
$handler->display->display_options['fields']['field_noticias_posicao']['id'] = 'field_noticias_posicao';
$handler->display->display_options['fields']['field_noticias_posicao']['table'] = 'field_data_field_noticias_posicao';
$handler->display->display_options['fields']['field_noticias_posicao']['field'] = 'field_noticias_posicao';
$handler->display->display_options['fields']['field_noticias_posicao']['label'] = '';
$handler->display->display_options['fields']['field_noticias_posicao']['element_label_colon'] = FALSE;
/* Campo: Conteúdo: Data */
$handler->display->display_options['fields']['field_noticias_data']['id'] = 'field_noticias_data';
$handler->display->display_options['fields']['field_noticias_data']['table'] = 'field_data_field_noticias_data';
$handler->display->display_options['fields']['field_noticias_data']['field'] = 'field_noticias_data';
$handler->display->display_options['fields']['field_noticias_data']['label'] = '';
$handler->display->display_options['fields']['field_noticias_data']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_noticias_data']['settings'] = array(
'format_type' => 'long',
'fromto' => 'both',
'multiple_number' => '',
'multiple_from' => '',
'multiple_to' => '',
'show_repeat_rule' => 'show',
);
/* Campo: Conteúdo: Updated date */
$handler->display->display_options['fields']['changed']['id'] = 'changed';
$handler->display->display_options['fields']['changed']['table'] = 'node';
$handler->display->display_options['fields']['changed']['field'] = 'changed';
$handler->display->display_options['fields']['changed']['date_format'] = 'long';
/* Sort criterion: Conteúdo: Posição da notícia (field_noticias_posicao) */
$handler->display->display_options['sorts']['field_noticias_posicao_value']['id'] = 'field_noticias_posicao_value';
$handler->display->display_options['sorts']['field_noticias_posicao_value']['table'] = 'field_data_field_noticias_posicao';
$handler->display->display_options['sorts']['field_noticias_posicao_value']['field'] = 'field_noticias_posicao_value';
$handler->display->display_options['sorts']['field_noticias_posicao_value']['expose']['label'] = 'Posição da notícia (field_noticias_posicao)';
/* Sort criterion: Conteúdo: Data (field_noticias_data) */
$handler->display->display_options['sorts']['field_noticias_data_value']['id'] = 'field_noticias_data_value';
$handler->display->display_options['sorts']['field_noticias_data_value']['table'] = 'field_data_field_noticias_data';
$handler->display->display_options['sorts']['field_noticias_data_value']['field'] = 'field_noticias_data_value';
$handler->display->display_options['sorts']['field_noticias_data_value']['order'] = 'DESC';
/* Sort criterion: Conteúdo: Updated date */
$handler->display->display_options['sorts']['changed']['id'] = 'changed';
$handler->display->display_options['sorts']['changed']['table'] = 'node';
$handler->display->display_options['sorts']['changed']['field'] = 'changed';
$handler->display->display_options['sorts']['changed']['order'] = 'DESC';
/* Filter criterion: Conteúdo: Publicado */
$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: Conteúdo: Tipo */
$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(
'noticias' => 'noticias',
'noticias_externas' => 'noticias_externas',
);
/* Filter criterion: Remove notícias que estão na galeira */
$handler->display->display_options['filters']['field_noticias_posicao_value']['id'] = 'field_noticias_posicao_value';
$handler->display->display_options['filters']['field_noticias_posicao_value']['table'] = 'field_data_field_noticias_posicao';
$handler->display->display_options['filters']['field_noticias_posicao_value']['field'] = 'field_noticias_posicao_value';
$handler->display->display_options['filters']['field_noticias_posicao_value']['ui_name'] = 'Remove notícias que estão na galeira';
$handler->display->display_options['filters']['field_noticias_posicao_value']['operator'] = 'not';
$handler->display->display_options['filters']['field_noticias_posicao_value']['value'] = array(
1 => '1',
);
/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'noticias-em-destaque';
$translatables['noticias_em_destaque'] = array(
t('Master'),
t('Notícias em destaque'),
t('more'),
t('Apply'),
t('Restaurar'),
t('Sort by'),
t('Asc'),
t('Desc'),
t('Updated date'),
t('Posição da notícia (field_noticias_posicao)'),
t('Page'),
);
SQL generated:
SELECT node.type AS node_type, node.title AS node_title, node.nid AS nid, node.changed AS node_changed, field_data_field_noticias_posicao.field_noticias_posicao_value AS field_data_field_noticias_posicao_field_noticias_posicao_val, field_data_field_noticias_data.field_noticias_data_value AS field_data_field_noticias_data_field_noticias_data_value, 'node' AS field_data_field_notext_link_node_entity_type, 'node' AS field_data_field_noticias_posicao_node_entity_type, 'node' AS field_data_field_noticias_data_node_entity_type
FROM
{node} node
LEFT JOIN {field_data_field_noticias_posicao} field_data_field_noticias_posicao ON node.nid = field_data_field_noticias_posicao.entity_id AND field_data_field_noticias_posicao.field_noticias_posicao_value = '1'
LEFT JOIN {field_data_field_noticias_data} field_data_field_noticias_data ON node.nid = field_data_field_noticias_data.entity_id AND (field_data_field_noticias_data.entity_type = 'node' AND field_data_field_noticias_data.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN ('noticias', 'noticias_externas')) AND (field_data_field_noticias_posicao.field_noticias_posicao_value IS NULL ) ))
ORDER BY field_data_field_noticias_posicao_field_noticias_posicao_val ASC, field_data_field_noticias_data_field_noticias_data_value DESC, node_changed DESC
The first left join is completely wrong: "field_data_field_noticias_posicao.field_noticias_posicao_value = '1'" should not be generated.
I believe this is a bug.
When I change the filter criteria for "one of" and select the other two values (2 and 3) the same view works perfectly.
See below.
Exported view:
$view = new view();
$view->name = 'noticias_em_destaque';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Notícias em destaque';
$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'] = 'Notícias em destaque';
$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['exposed_form']['type'] = 'basic';
$handler->display->display_options['exposed_form']['options']['reset_button_label'] = 'Restaurar';
$handler->display->display_options['pager']['type'] = 'none';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Campo: Conteúdo: Tipo */
$handler->display->display_options['fields']['type']['id'] = 'type';
$handler->display->display_options['fields']['type']['table'] = 'node';
$handler->display->display_options['fields']['type']['field'] = 'type';
$handler->display->display_options['fields']['type']['label'] = '';
$handler->display->display_options['fields']['type']['exclude'] = TRUE;
$handler->display->display_options['fields']['type']['element_label_colon'] = FALSE;
/* Campo: Conteúdo: Título */
$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'] = '';
$handler->display->display_options['fields']['title']['exclude'] = TRUE;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = FALSE;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = FALSE;
$handler->display->display_options['fields']['title']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['title']['link_to_node'] = FALSE;
/* Campo: Path do conteúdo (link interno) */
$handler->display->display_options['fields']['path']['id'] = 'path';
$handler->display->display_options['fields']['path']['table'] = 'node';
$handler->display->display_options['fields']['path']['field'] = 'path';
$handler->display->display_options['fields']['path']['ui_name'] = 'Path do conteúdo (link interno)';
$handler->display->display_options['fields']['path']['label'] = '';
$handler->display->display_options['fields']['path']['exclude'] = TRUE;
$handler->display->display_options['fields']['path']['element_label_colon'] = FALSE;
/* Campo: Link externo */
$handler->display->display_options['fields']['field_notext_link']['id'] = 'field_notext_link';
$handler->display->display_options['fields']['field_notext_link']['table'] = 'field_data_field_notext_link';
$handler->display->display_options['fields']['field_notext_link']['field'] = 'field_notext_link';
$handler->display->display_options['fields']['field_notext_link']['ui_name'] = 'Link externo';
$handler->display->display_options['fields']['field_notext_link']['label'] = '';
$handler->display->display_options['fields']['field_notext_link']['exclude'] = TRUE;
$handler->display->display_options['fields']['field_notext_link']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_notext_link']['click_sort_column'] = 'url';
$handler->display->display_options['fields']['field_notext_link']['type'] = 'link_plain';
/* Campo: Título e link - notícia interna */
$handler->display->display_options['fields']['views_conditional']['id'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional']['table'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional']['field'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional']['ui_name'] = 'Título e link - notícia interna';
$handler->display->display_options['fields']['views_conditional']['label'] = '';
$handler->display->display_options['fields']['views_conditional']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['views_conditional']['if'] = 'type';
$handler->display->display_options['fields']['views_conditional']['condition'] = '1';
$handler->display->display_options['fields']['views_conditional']['equalto'] = 'Notícias';
$handler->display->display_options['fields']['views_conditional']['then'] = '[title]';
/* Campo: Título e link - notícia externa */
$handler->display->display_options['fields']['views_conditional_1']['id'] = 'views_conditional_1';
$handler->display->display_options['fields']['views_conditional_1']['table'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional_1']['field'] = 'views_conditional';
$handler->display->display_options['fields']['views_conditional_1']['ui_name'] = 'Título e link - notícia externa';
$handler->display->display_options['fields']['views_conditional_1']['label'] = '';
$handler->display->display_options['fields']['views_conditional_1']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['views_conditional_1']['if'] = 'type';
$handler->display->display_options['fields']['views_conditional_1']['condition'] = '2';
$handler->display->display_options['fields']['views_conditional_1']['equalto'] = 'Notícias';
$handler->display->display_options['fields']['views_conditional_1']['then'] = '[title]';
/* Campo: Conteúdo: Posição da notícia */
$handler->display->display_options['fields']['field_noticias_posicao']['id'] = 'field_noticias_posicao';
$handler->display->display_options['fields']['field_noticias_posicao']['table'] = 'field_data_field_noticias_posicao';
$handler->display->display_options['fields']['field_noticias_posicao']['field'] = 'field_noticias_posicao';
$handler->display->display_options['fields']['field_noticias_posicao']['label'] = '';
$handler->display->display_options['fields']['field_noticias_posicao']['element_label_colon'] = FALSE;
/* Campo: Conteúdo: Data */
$handler->display->display_options['fields']['field_noticias_data']['id'] = 'field_noticias_data';
$handler->display->display_options['fields']['field_noticias_data']['table'] = 'field_data_field_noticias_data';
$handler->display->display_options['fields']['field_noticias_data']['field'] = 'field_noticias_data';
$handler->display->display_options['fields']['field_noticias_data']['label'] = '';
$handler->display->display_options['fields']['field_noticias_data']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_noticias_data']['settings'] = array(
'format_type' => 'long',
'fromto' => 'both',
'multiple_number' => '',
'multiple_from' => '',
'multiple_to' => '',
'show_repeat_rule' => 'show',
);
/* Campo: Conteúdo: Updated date */
$handler->display->display_options['fields']['changed']['id'] = 'changed';
$handler->display->display_options['fields']['changed']['table'] = 'node';
$handler->display->display_options['fields']['changed']['field'] = 'changed';
$handler->display->display_options['fields']['changed']['date_format'] = 'long';
/* Sort criterion: Conteúdo: Posição da notícia (field_noticias_posicao) */
$handler->display->display_options['sorts']['field_noticias_posicao_value']['id'] = 'field_noticias_posicao_value';
$handler->display->display_options['sorts']['field_noticias_posicao_value']['table'] = 'field_data_field_noticias_posicao';
$handler->display->display_options['sorts']['field_noticias_posicao_value']['field'] = 'field_noticias_posicao_value';
$handler->display->display_options['sorts']['field_noticias_posicao_value']['expose']['label'] = 'Posição da notícia (field_noticias_posicao)';
/* Sort criterion: Conteúdo: Data (field_noticias_data) */
$handler->display->display_options['sorts']['field_noticias_data_value']['id'] = 'field_noticias_data_value';
$handler->display->display_options['sorts']['field_noticias_data_value']['table'] = 'field_data_field_noticias_data';
$handler->display->display_options['sorts']['field_noticias_data_value']['field'] = 'field_noticias_data_value';
$handler->display->display_options['sorts']['field_noticias_data_value']['order'] = 'DESC';
/* Sort criterion: Conteúdo: Updated date */
$handler->display->display_options['sorts']['changed']['id'] = 'changed';
$handler->display->display_options['sorts']['changed']['table'] = 'node';
$handler->display->display_options['sorts']['changed']['field'] = 'changed';
$handler->display->display_options['sorts']['changed']['order'] = 'DESC';
/* Filter criterion: Conteúdo: Publicado */
$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: Conteúdo: Tipo */
$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(
'noticias' => 'noticias',
'noticias_externas' => 'noticias_externas',
);
/* Filter criterion: Remove notícias que estão na galeira */
$handler->display->display_options['filters']['field_noticias_posicao_value']['id'] = 'field_noticias_posicao_value';
$handler->display->display_options['filters']['field_noticias_posicao_value']['table'] = 'field_data_field_noticias_posicao';
$handler->display->display_options['filters']['field_noticias_posicao_value']['field'] = 'field_noticias_posicao_value';
$handler->display->display_options['filters']['field_noticias_posicao_value']['ui_name'] = 'Remove notícias que estão na galeira';
$handler->display->display_options['filters']['field_noticias_posicao_value']['value'] = array(
2 => '2',
3 => '3',
);
/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'noticias-em-destaque';
$translatables['noticias_em_destaque'] = array(
t('Master'),
t('Notícias em destaque'),
t('more'),
t('Apply'),
t('Restaurar'),
t('Sort by'),
t('Asc'),
t('Desc'),
t('Updated date'),
t('Posição da notícia (field_noticias_posicao)'),
t('Page'),
);
SQL generated:
SELECT node.type AS node_type, node.title AS node_title, node.nid AS nid, node.changed AS node_changed, field_data_field_noticias_posicao.field_noticias_posicao_value AS field_data_field_noticias_posicao_field_noticias_posicao_val, field_data_field_noticias_data.field_noticias_data_value AS field_data_field_noticias_data_field_noticias_data_value, 'node' AS field_data_field_notext_link_node_entity_type, 'node' AS field_data_field_noticias_posicao_node_entity_type, 'node' AS field_data_field_noticias_data_node_entity_type
FROM
{node} node
INNER JOIN {field_data_field_noticias_posicao} field_data_field_noticias_posicao ON node.nid = field_data_field_noticias_posicao.entity_id AND (field_data_field_noticias_posicao.entity_type = 'node' AND field_data_field_noticias_posicao.deleted = '0')
LEFT JOIN {field_data_field_noticias_data} field_data_field_noticias_data ON node.nid = field_data_field_noticias_data.entity_id AND (field_data_field_noticias_data.entity_type = 'node' AND field_data_field_noticias_data.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN ('noticias', 'noticias_externas')) AND (field_data_field_noticias_posicao.field_noticias_posicao_value IN ('2', '3')) ))
ORDER BY field_data_field_noticias_posicao_field_noticias_posicao_val ASC, field_data_field_noticias_data_field_noticias_data_value DESC, node_changed DESC
Now if we compare only the filter and sort portion of the sql:
--- using "is none of" (1)
LEFT JOIN {field_data_field_noticias_posicao} field_data_field_noticias_posicao ON node.nid = field_data_field_noticias_posicao.entity_id AND field_data_field_noticias_posicao.field_noticias_posicao_value = '1'
LEFT JOIN {field_data_field_noticias_data} field_data_field_noticias_data ON node.nid = field_data_field_noticias_data.entity_id AND (field_data_field_noticias_data.entity_type = 'node' AND field_data_field_noticias_data.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN ('noticias', 'noticias_externas')) AND (field_data_field_noticias_posicao.field_noticias_posicao_value IS NULL ) ))
ORDER BY field_data_field_noticias_posicao_field_noticias_posicao_val ASC, field_data_field_noticias_data_field_noticias_data_value DESC, node_changed DESC
---> using "is one of" (2,3)
INNER JOIN {field_data_field_noticias_posicao} field_data_field_noticias_posicao ON node.nid = field_data_field_noticias_posicao.entity_id AND (field_data_field_noticias_posicao.entity_type = 'node' AND field_data_field_noticias_posicao.deleted = '0')
LEFT JOIN {field_data_field_noticias_data} field_data_field_noticias_data ON node.nid = field_data_field_noticias_data.entity_id AND (field_data_field_noticias_data.entity_type = 'node' AND field_data_field_noticias_data.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN ('noticias', 'noticias_externas')) AND (field_data_field_noticias_posicao.field_noticias_posicao_value IN ('2', '3')) ))
ORDER BY field_data_field_noticias_posicao_field_noticias_posicao_val ASC, field_data_field_noticias_data_field_noticias_data_value DESC, node_changed DESC
The "order by "is the same so the problem resides on the filter!
I believe the bug is at the left join for table "noticias_posicao" . The usage of a left join is probrably correct because "none of" should try to get null/empty values. But the condition on that left join is wrong since the "equal" operator should not be used here!
Perhaps the condition for the field "noticias_posicao" should be removed from the left join (otherwise the filter will not work) and moved to "where clausule". In this case the correct operator could be "<>" or "not in (list of value)". For this option of filtering I dont know how to make null/empty values valid records... perhaps using mysql/postgresql functions to convert null/empty to a "true" condition? (for example the value zero would produce true).
If I can detail or help any further to correct this bug I will be happy to help.
| Comment | File | Size | Author |
|---|---|---|---|
| #10 | views-2069269-10.patch | 1.84 KB | mustanggb |
| #6 | views-2069269-filter-criteria-is-none-of-wrong-query.patch | 1.52 KB | mattew |
Comments
Comment #1
gilsbert commentedHi.
Recently I got the very same situation in a different project: a list of integer values necessary for filter and sort criterias.
I'm using postgresql database and this issue might be restricted to it.
Is someone interested/capable to help me fix this issue?
I have no idea where to look in views's module to write a patch but I can point what should be done.
Generated SQL below!
SELECT node.nid AS nid, node.title AS node_title, field_data_field_parceria_peso.field_parceria_peso_value AS field_data_field_parceria_peso_field_parceria_peso_value, 'node' AS field_data_field_parceria_link_node_entity_type, 'node' AS field_data_field_parceria_logotipo_node_entity_type, 'node' AS field_data_body_node_entity_type
FROM
{node} node
LEFT JOIN {field_data_field_parceria_peso} field_data_field_parceria_peso ON node.nid = field_data_field_parceria_peso.entity_id AND field_data_field_parceria_peso.field_parceria_peso_value = '99'
WHERE (( (node.status = '1') AND (node.type IN ('parceria')) AND (field_data_field_parceria_peso.field_parceria_peso_value IS NULL ) ))
ORDER BY field_data_field_parceria_peso_field_parceria_peso_value ASC, node_title ASC
LIMIT 10 OFFSET 0
The bug is at two places:
1) LEFT JOIN {field_data_field_parceria_peso} field_data_field_parceria_peso ON node.nid = field_data_field_parceria_peso.entity_id AND field_data_field_parceria_peso.field_parceria_peso_value = '99'
The "none of" value should not be used here because this will make all valid records (everything different of 99) return "null" as the value of this field!
2) WHERE (( (node.status = '1') AND (node.type IN ('parceria')) AND (field_data_field_parceria_peso.field_parceria_peso_value IS NULL ) ))
The field is compared with null but this is a result of the previous bug!
In another words: the "none of" operation is working but the field became unavailable for others parts of the SQL (ordery by for example).
The generated SQL should be this one:
SELECT node.nid AS nid, node.title AS node_title, field_data_field_parceria_peso.field_parceria_peso_value AS field_data_field_parceria_peso_field_parceria_peso_value, 'node' AS field_data_field_parceria_link_node_entity_type, 'node' AS field_data_field_parceria_logotipo_node_entity_type, 'node' AS field_data_body_node_entity_type
FROM
{node} node
LEFT JOIN {field_data_field_parceria_peso} field_data_field_parceria_peso ON node.nid = field_data_field_parceria_peso.entity_id
WHERE (( (node.status = '1') AND (node.type IN ('parceria')) AND field_data_field_parceria_peso.field_parceria_peso_value <> '99' ))
ORDER BY field_data_field_parceria_peso_field_parceria_peso_value ASC, node_title ASC
LIMIT 10 OFFSET 0
In summary: the "none of" stops to act in the "left join" and is used only in the "where".
If more than one value is selected for "none of" the operator must be changed from "<>" to "not in ( value1, value2,...,valuen )" or a sequence of "<> with and".
I don't know if the new approach would bring others concerns but I'm avaliable to help improve it.
Regards,
Gilsberty
Comment #2
mattew commentedThis issue seems to be the same than https://drupal.org/node/1836138
And the problem seems to exists on D6 too: https://drupal.org/node/1450918
Comment #3
mattew commentedI found a fix, I'll submit a patch to see if tests pass.
Comment #4
mattew commentedComment #6
mattew commentedComment #7
mattew commentedOK, the test works, but the query is still wrong...
I have a problem with the LEFT JOIN, it should not being used when dealing with a "Is none of" (not) and a OR condition...
LEFT JOIN is build using "AND" condition instead of OR, despite the fact that
$join->extra_type = 'OR';is provided before building the join. See /includes/handlers.inc, see line 967 and more:
Without applying the patch:
With the patch :
If I don't want any fields in the LEFT JOIN, I have to empty the value instead of providing the operator:
What the point of this foreach loop ? In case of a "not", why should we build a LEFT JOIN with conditions, as the filters are provided in the WHERE clause...
I don't understand, a Views guru should have a look at this...
Comment #8
mattew commentedComment #9
mustanggb commentedAn example use-case with OR'd filter groups:
Expected sudo-SQL:
Observed sudo-SQL:
The extra join condition is prematurely filtering out results that should be available to the second OR filter group.
I'm testing out the attached patch.
EDIT: Added null check back in.
Comment #10
mustanggb commentedTurn out the null condition is needed, but should be OR'd rather than AND'd.
Comment #11
chris matthews commentedThe 2 year old patch in #10 to handlers.inc does not apply to the latest views 7.x-3.x-dev and if still relevant needs to be rerolled.
Comment #12
andrew answer commentedBecause of much changes in Views I cannot reroll patch and recommend to rewrite it, if problem still exist.