Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
I hope I followed the correct procedure since I searched and couldn't find a bug report similar to what I have here. I get the following error when I tried to create a taxonomy filter in one of the views I created
user warning: 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 ')' at line 1 query: SELECT * FROM term_data td WHERE td.tid IN () in /home/national/public_html/sites/all/modules/views/modules/taxonomy/views_handler_filter_term_node_tid.inc on line 295.
Here is the export:
$view = new view;
$view->name = 'Em_video';
$view->description = 'Embedded videos from youTube etc';
$view->tag = 'videos';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('fields', array(
'title' => array(
'label' => '',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'link_to_node' => 1,
'exclude' => 0,
'id' => 'title',
'table' => 'node',
'field' => 'title',
'relationship' => 'none',
),
'field_em_video_embed' => array(
'label' => '',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'link_to_node' => 0,
'label_type' => 'none',
'format' => 'video_thumbnail',
'multiple' => array(
'group' => TRUE,
'multiple_number' => '',
'multiple_from' => '',
'multiple_reversed' => FALSE,
),
'exclude' => 0,
'id' => 'field_em_video_embed',
'table' => 'node_data_field_em_video',
'field' => 'field_em_video_embed',
'relationship' => 'none',
),
'tid' => array(
'label' => 'Gallery',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'type' => 'separator',
'separator' => ', ',
'link_to_taxonomy' => 1,
'limit' => 1,
'vids' => array(
'11' => 11,
'2' => 0,
'7' => 0,
'6' => 0,
'10' => 0,
'8' => 0,
'4' => 0,
'1' => 0,
'3' => 0,
'9' => 0,
'5' => 0,
),
'exclude' => 0,
'id' => 'tid',
'table' => 'term_node',
'field' => 'tid',
'relationship' => 'none',
),
));
$handler->override_option('sorts', array(
'created' => array(
'order' => 'ASC',
'granularity' => 'minute',
'id' => 'created',
'table' => 'node',
'field' => 'created',
'relationship' => 'none',
),
));
$handler->override_option('filters', array(
'type' => array(
'operator' => 'in',
'value' => array(
'em_video' => 'em_video',
'video' => 'video',
),
'group' => '0',
'exposed' => FALSE,
'expose' => array(
'operator' => FALSE,
'label' => '',
),
'id' => 'type',
'table' => 'node',
'field' => 'type',
'relationship' => 'none',
'override' => array(
'button' => 'Override',
),
),
'tid' => array(
'operator' => 'or',
'value' => '',
'group' => '0',
'exposed' => TRUE,
'expose' => array(
'use_operator' => 0,
'operator' => 'tid_op',
'identifier' => 'tid',
'label' => 'Search video types',
'optional' => 1,
'single' => 1,
'remember' => 0,
),
'type' => 'textfield',
'limit' => TRUE,
'vid' => '11',
'id' => 'tid',
'table' => 'term_node',
'field' => 'tid',
'hierarchy' => 0,
'configure_hs' => '<a href="/admin/build/views/hs_config/Em_video/default/tid">Configure Hierarchical Select</a>',
'relationship' => 'none',
'reduce_duplicates' => 0,
),
));
$handler->override_option('access', array(
'type' => 'none',
));
$handler->override_option('cache', array(
'type' => 'none',
));
$handler->override_option('use_ajax', TRUE);
$handler->override_option('items_per_page', 100);
$handler->override_option('use_pager', '1');
$handler->override_option('style_plugin', 'grid');
$handler->override_option('style_options', array(
'grouping' => '',
'columns' => '6',
'alignment' => 'horizontal',
'fill_single_line' => 1,
));
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'videos');
$handler->override_option('menu', array(
'type' => 'normal',
'title' => 'Videos',
'description' => 'Video gallery',
'weight' => '0',
'name' => 'primary-links',
));
$handler->override_option('tab_options', array(
'type' => 'none',
'title' => '',
'description' => '',
'weight' => 0,
'name' => 'navigation',
));
Here is the query:
SELECT node.nid AS nid,
node.title AS node_title,
node_data_field_em_video.field_em_video_embed AS node_data_field_em_video_field_em_video_embed,
node_data_field_em_video.field_em_video_value AS node_data_field_em_video_field_em_video_value,
node_data_field_em_video.field_em_video_provider AS node_data_field_em_video_field_em_video_provider,
node_data_field_em_video.field_em_video_data AS node_data_field_em_video_field_em_video_data,
node_data_field_em_video.field_em_video_version AS node_data_field_em_video_field_em_video_version,
node_data_field_em_video.field_em_video_duration AS node_data_field_em_video_field_em_video_duration,
node.type AS node_type,
node.vid AS node_vid,
DATE_FORMAT((FROM_UNIXTIME(node.created) + INTERVAL 7200 SECOND), '%Y%m%d%H%i') AS node_created_minute
FROM node node
LEFT JOIN content_type_em_video node_data_field_em_video ON node.vid = node_data_field_em_video.vid
WHERE node.type in ('em_video', 'video')
ORDER BY node_created_minute ASC
Comment | File | Size | Author |
---|---|---|---|
#4 | views-SQL-error-921564.patch | 618 bytes | wjaspers |
Comments
Comment #1
dawehnerAwesome report!
Could you have a look at #721774: HS Taxonomy Views + Views 3 + taxonomy term filter: You have an error in your SQL syntax whether this seems to be the same issue?
Comment #2
vip_sa CreditAttribution: vip_sa commentedI have views version 6x2.11 and that is fr views 3 but I disabled the module in any case and it seems to fix it. Thank you very much for the feedback
Comment #3
dawehnerSo this is fixed?
You can help the maintainer of modules and set the status to fixed if any issue is "fixed".
Comment #4
wjaspers CreditAttribution: wjaspers commentedHierarchical select didn't trigger this issue for me.
I patched my views handler, and the SQL error went away.
Give this a shot.
It may need to be tested.
Comment #5
dawehnerI don't understand the patch. Why should tid be a string here?
Comment #6
wjaspers CreditAttribution: wjaspers commentedIt fixed this bug in my instance.
It seems as if text is getting into TID from another source at this point.
MySQL functions the same way with both quoted and unquoted numbers.
In this way, if the source value is wrong, the query still runs -- without causing an SQL error.
Comment #7
merlinofchaos CreditAttribution: merlinofchaos commentedMySQL does but pgsql does NOT. This would break under pgsql.
Comment #8
dawehnerSo
Additonal i still think this belongs into Hierarchical Select.
Comment #9
brunorios1 CreditAttribution: brunorios1 commentedsubscribing...
Comment #10
MustangGB CreditAttribution: MustangGB commented