jQuery Query Builder中IN运算符多选项SQL生成异常求助

你碰到的这个问题很典型——Query Builder默认没有识别到你的selectize控件是多选模式，所以把所有选中值当成了单个字符串来处理，最终导致IN子句格式错误。我之前也踩过这个坑，咱们一步步来修复：

问题根源 #

虽然你启用了selectize的多选，但Query Builder的过滤器没有明确标记为多值字段，同时默认的valueGetter只会返回selectize输出的逗号分隔字符串，而不是Query Builder需要的数组格式，所以生成SQL时就把整个字符串当成了单个值。

解决方案步骤 #

1. 标记字段为多选：在过滤器配置里添加multiple: true，告诉Query Builder这个字段支持多值
2. 自定义值获取方法：添加valueGetter，从selectize实例中直接获取选中的数组，而不是默认的字符串
3. 完善值设置方法：确保valueSetter也能正确处理数组类型的输入

修改后的完整代码 #

<!DOCTYPE html>
<html lang="en">
<head>
<title>x86 Query Builder</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<link href="http://querybuilder.js.org/dist/selectize/dist/css/selectize.bootstrap3.css" rel="stylesheet">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-select/1.12.4/css/bootstrap-select.min.css">
<link href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-formhelpers/2.3.0/css/bootstrap-formhelpers.css" rel="stylesheet" />
<link href="http://querybuilder.js.org/dist/jQuery-QueryBuilder/dist/css/query-builder.default.min.css" rel="stylesheet">
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/sweetalert/1.1.3/sweetalert.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.0/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script src="http://querybuilder.js.org/dist/selectize/dist/js/standalone/selectize.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-select/1.12.4/js/bootstrap-select.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-formhelpers/2.3.0/js/bootstrap-formhelpers.js"></script>
<script src="http://querybuilder.js.org/dist/jQuery-QueryBuilder/dist/js/query-builder.standalone.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/sweetalert/1.1.3/sweetalert.min.js"></script>
</head>
<body>
<div class="container">
<div id="builder"></div>
<button class="btn btn-info" id="btn-get-sql">SQL</button>
<script>
data = '[{"id": 1,"name": "Germany"},{"id": 2,"name": "Austria"},{"id": 3,"name": "USA"},{"id": 4,"name": "Australia"}]';
$('#builder').on('afterCreateRuleInput.queryBuilder', function(e, rule) {
if (rule.filter.plugin == 'selectize') {
rule.$el.find('.rule-value-container').css('min-width', '200px')
.find('.selectize-control').removeClass('form-control');}
});
$('#builder').queryBuilder({
plugins: [
'bt-tooltip-errors',
'not-group'
],
filters: [{
id: 'country',
label: 'country',
type: 'string',
plugin: 'selectize',
operators: ['equal','in'],
multiple: true, // 关键：标记为多值字段
value_separator: ',', // 配合多值处理的分隔符
plugin_config: {
valueField: 'name',
labelField: 'name',
searchField: 'name',
sortField: 'name',
create: false,
maxItems: null, // 明确允许多选，去掉限制单个选择的配置
plugins: ['remove_button'],
onInitialize: function() {
var that = this;
JSON.parse(data).forEach(function(item) {
that.addOption(item);
});
}
},
valueGetter: function(rule) {
// 从selectize实例获取选中的数组值
return rule.$el.find('.rule-value-container input')[0].selectize.getValue();
},
valueSetter: function(rule, value) {
// 处理数组类型的设置值
rule.$el.find('.rule-value-container input')[0].selectize.setValue(value);
}
}]
});
$('#btn-get-sql').on('click', function() {
var result = $('#builder').queryBuilder('getSQL', false);
if (result.sql.length) {
alert(result.sql);
}
});
</script>
</div>
</body>
</html>

关键修改点说明 #

• multiple: true：这是让Query Builder正确识别多值字段的核心配置，没有它的话，即使返回数组也会被当成字符串处理
• valueGetter：直接调用selectize的getValue()方法，多选模式下该方法会返回选中值的数组
• 移除了maxItems:1的限制，确保selectize允许用户选择多个选项

现在测试一下，选中多个国家后点击SQL按钮，就能生成你预期的country IN('Australia','Germany')格式的SQL语句了。

内容的提问来源于stack exchange，提问作者JuniorDev