I'm trying to create a keyword search based on a field.
This is the error I get in an Alert:
An AJAX HTTP error occurred.
HTTP Result Code: 500
Debugging information follows.
Path: /admin/structure/views/ajax/preview/test
StatusText: Service unavailable (with message)
ResponseText: 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 ')))) subquery' at line 2: SELECT COUNT(*) AS expression
FROM
(SELECT 1 AS expression
FROM
{node} node
LEFT JOIN {field_data_field_bizhours} field_data_field_bizhours ON node.nid = field_data_field_bizhours.entity_id AND (field_data_field_bizhours.entity_type = :views_join_condition_0 AND field_data_field_bizhours.deleted = :views_join_condition_1)
WHERE (( (node.type IN (:db_condition_placeholder_0)) AND (node.status = :db_condition_placeholder_1) AND()))) subquery; Array
(
[:db_condition_placeholder_0] => lodging
[:db_condition_placeholder_1] => 0
[:views_join_condition_0] => node
[:views_join_condition_1] => 0
)
in views_plugin_pager->execute_count_query() (line 140 of Z:\xampp\htdocs\D7\sites\all\modules\views\plugins\views_plugin_pager.inc).
-----------------------------------------------
Using Mini or Full Pager causes the error to appear. If I change the pager to "Display a specified number of items" or "Display all items" the error disappears.
Also, as an exposed filter for field I would like to make the filter optional and that's not available in the settings.
| Comment | File | Size | Author |
|---|---|---|---|
| #2 | views-export.txt | 11.84 KB | enkara |
Comments
Comment #1
dawehnerCan you describe some ways to reproduce the problem?
For example on http://drupal.org/node/571990 which you read, because you created this issue, it's described that an export helps a lot.
As always: help us to help you :)
Comment #2
enkara commentedSame issue here.
I attach an export of my view.
Thank you
Comment #3
silverflame757 commentedCreate a Node view with:
Fields for the view:
Node:Title
Fields: Business Hours
Filter view by:
Node: Type = Lodging
Node: Published Yes
Fields: field_bizhours - value (exposed field using "Contains Any Word" or "Contains All Words" as Operator)
Basic Settings:
Use pager: Paged, 10 items
Result: A error alert pops us which is what I included in my previous post. Changing the Pager setting to
"Display a specified number of items" or "Display all items" results in no error. Exposed field filter should be optional but it doesn't have a setting for that so I only see nodes with field_bizhours - value and not ones without a value.
Export provided:
$view = new view;
$view->name = 'test';
$view->description = '';
$view->tag = '';
$view->base_table = 'node';
$view->human_name = 'test';
$view->core = 7;
$view->api_version = '3.0-alpha1';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
/* Display: Defaults */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->display->display_options['access']['type'] = 'none';
$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['pager']['type'] = 'full';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Field: Node: 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']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['title']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['title']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['title']['alter']['external'] = 0;
$handler->display->display_options['fields']['title']['alter']['trim'] = 0;
$handler->display->display_options['fields']['title']['alter']['nl2br'] = 0;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['title']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['title']['alter']['html'] = 0;
$handler->display->display_options['fields']['title']['element_label_colon'] = 1;
$handler->display->display_options['fields']['title']['element_default_classes'] = 1;
$handler->display->display_options['fields']['title']['hide_empty'] = 0;
$handler->display->display_options['fields']['title']['empty_zero'] = 0;
$handler->display->display_options['fields']['title']['link_to_node'] = 1;
/* Field: Fields: Business Hours - Monday */
$handler->display->display_options['fields']['field_bizhours']['id'] = 'field_bizhours';
$handler->display->display_options['fields']['field_bizhours']['table'] = 'field_data_field_bizhours';
$handler->display->display_options['fields']['field_bizhours']['field'] = 'field_bizhours';
$handler->display->display_options['fields']['field_bizhours']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['field_bizhours']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['field_bizhours']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['field_bizhours']['alter']['external'] = 0;
$handler->display->display_options['fields']['field_bizhours']['alter']['trim'] = 0;
$handler->display->display_options['fields']['field_bizhours']['alter']['nl2br'] = 0;
$handler->display->display_options['fields']['field_bizhours']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['field_bizhours']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['field_bizhours']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['field_bizhours']['alter']['html'] = 0;
$handler->display->display_options['fields']['field_bizhours']['element_label_colon'] = 1;
$handler->display->display_options['fields']['field_bizhours']['element_default_classes'] = 1;
$handler->display->display_options['fields']['field_bizhours']['hide_empty'] = 0;
$handler->display->display_options['fields']['field_bizhours']['empty_zero'] = 0;
$handler->display->display_options['fields']['field_bizhours']['field_api_classes'] = 0;
/* Filter: Node: 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';
/* Filter: Node: 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(
'lodging' => 'lodging',
);
/* Filter: Fields: Business Hours - Monday (field_bizhours) - value */
$handler->display->display_options['filters']['field_bizhours_value']['id'] = 'field_bizhours_value';
$handler->display->display_options['filters']['field_bizhours_value']['table'] = 'field_data_field_bizhours';
$handler->display->display_options['filters']['field_bizhours_value']['field'] = 'field_bizhours_value';
$handler->display->display_options['filters']['field_bizhours_value']['operator'] = 'word';
$handler->display->display_options['filters']['field_bizhours_value']['exposed'] = TRUE;
$handler->display->display_options['filters']['field_bizhours_value']['expose']['operator'] = 'field_bizhours_value_op';
$handler->display->display_options['filters']['field_bizhours_value']['expose']['label'] = 'Business Hours';
$handler->display->display_options['filters']['field_bizhours_value']['expose']['identifier'] = 'field_bizhours_value';
$handler->display->display_options['filters']['field_bizhours_value']['expose']['optional'] = TRUE;
$handler->display->display_options['filters']['field_bizhours_value']['expose']['single'] = TRUE;
$translatables['test'] = array(
t('Defaults'),
t('more'),
t('Apply'),
t('Reset'),
t('Sort By'),
t('Asc'),
t('Desc'),
t('Items per page'),
t('- All -'),
t('Offset'),
t('Title'),
t('Business Hours - Monday'),
t('Business Hours'),
);
Comment #4
silverflame757 commentedI notice the view export says I'm using [$view->api_version = '3.0-alpha1';] but I'm using 7.x-3.x-dev (2011-Mar-01). I don't know why it says 3.0-alpha1 because I uninstalled alpha and replaced with dev.
Comment #5
merlinofchaos commentedThat's the views API version which does not necessarily match the released version.
Comment #6
dawehnerThis is basically a duplicate of #1054272: String filters are non optional anymore
Comment #7
enkara commentedIt may be that issue, but it does not solve the problem for me. I've applied the patch but I get the same error., I don't know if silverflame757 has solved it, then maybe it's my views module, I think I've already applied several patches. Is there a new alpha release going out soon?