Level 75
I suggest going over their demo for more ideas: https://querybuilder.js.org/demo.html
I am using jQuery QueryBuilder to apply the filters on data. Below is the code snippet for it. But now the issue is after selecting in operator from dropdown and giving values with comma separated, it's not generating corrrect SQL, that's why I am getting empty records.
I have tried different ways by giving values with comma separated, then by using ',' in between each value also tried with | separator. Please suggest.
<script type="text/javascript">
jQuery(document).ready(function ($) {
let rules_basic = {!! empty($oldDataFilter) ? '""' : ($oldDataFilter) !!};
let ntData = JSON.parse($('#nt_data').val());
let queryBuilder = $('#querybuilder');
let filters = [];
let operators = [
{ type: 'equal', optgroup: 'custom', nb_inputs: 1, multiple: false, apply_to: ['number', 'string'] },
{ type: 'not_equal', optgroup: 'custom', nb_inputs: 1, multiple: false, apply_to: ['number', 'string'] },
{ type: 'greater', optgroup: 'custom', nb_inputs: 1, multiple: false, apply_to: ['number', 'string'] },
{ type: 'less', optgroup: 'custom', nb_inputs: 1, multiple: false, apply_to: ['number', 'string'] },
{ type: 'greater_or_equal', optgroup: 'custom', nb_inputs: 1, multiple: false, apply_to: ['number', 'string'] },
{ type: 'less_or_equal', optgroup: 'custom', nb_inputs: 1, multiple: false, apply_to: ['number', 'string'] },
{ type: 'contains', optgroup: 'custom', nb_inputs: 1, multiple: false, apply_to: ['string'] },
{ type: 'begins_with', optgroup: 'custom', nb_inputs: 1, multiple: false, apply_to: ['string'] },
{ type: 'ends with', optgroup: 'custom', nb_inputs: 1, multiple: false, apply_to: ['string'] },
{ type: 'is_not_null', optgroup: 'custom', nb_inputs: 0, multiple: false, apply_to: ['string'] },
{ type: 'in', optgroup: 'custom', nb_inputs: 1, multiple: false, apply_to: ['string'], separator: '|' },
{ type: 'not_in', optgroup: 'custom', nb_inputs: 1, multiple: false, apply_to: ['string'], separator: '|' }
];
for(i = 0; i < ntData.length; i++) {
let ntDataType = ntData[i].Type.replace(/[^a-zA-Z]/g, '');
switch (ntDataType) {
case 'int':
ntDataType = 'integer';
case 'varchar':
ntDataType = 'string';
case 'double':
ntDataType = 'double';
case 'datetime':
ntDataType = 'datetime';
case 'tinytext':
ntDataType = 'string';
}
let newColumn = {
id: ntData[i].Field,
type: ntDataType,
value_separator: "|"
};
if (ntDataType === 'string') {
newColumn.input = 'text';
newColumn.value_separator = '|';
} else if (ntDataType === 'datetime') {
newColumn.plugin = 'datepicker';
newColumn.value_separator = '|';
newColumn.plugin_config = {
'language': 'en',
'dateFormat': "yyyy-mm-dd",
'autoClose': true,
'timepicker': true,
'timeFormat': 'hh:ii',
'onSelect': function (formattedDate, date, inst) {
$(inst.el).trigger('change');
}
};
newColumn.validation = {
'callback': function (value, rule) {
let parameters = value.toString().split(",");
let errorCount = 0;
parameters.forEach(function (val) {
if (!moment(val).isValid()) {
errorCount++;
}
});
if (errorCount > 0) {
return 'Not all vales are a correct date'
} else if (errorCount === 0) {
return true;
}
return moment(value).isValid() ? true : 'Not a correct date';
}
};
// newColumn.operators = ['equal', 'not_equal', 'less', 'less_or_equal', 'greater', 'greater_or_equal', 'between', 'not_between', 'is_null', 'is_not_null'];
} else if (ntDataType === 'integer') {
newColumn.input = 'text';
newColumn.value_separator = '|';
newColumn.validation = {
'callback': function (value, rule) {
let parameters = value.toString().split(",");
let errorCount = 0;
parameters.forEach(function (val) {
if (val === !Number.isInteger(val, 10) || isNaN(val)) {
errorCount++;
}
});
if (errorCount > 0) {
return 'Not all vales are an integer numbers'
} else if (errorCount === 0) {
return true;
}
return Number.isInteger(value) ? true : 'Not an integer number';
}
}
} else if (ntDataType === 'double') {
newColumn.input = 'text';
newColumn.value_separator = '|';
newColumn.validation = {
'callback': function (value, rule) {
let parameters = value.toString().split(",");
let errorCount = 0;
parameters.forEach(function (val) {
if (val === !$.isNumeric(value) || isNaN(val)) {
errorCount++;
}
});
if (errorCount > 0) {
return 'Not all vales are an float numbers'
} else if (errorCount === 0) {
return true;
}
return $.isNumeric(value) ? true : 'Not a double number';
}
}
}
filters.push(newColumn);
}
queryBuilder.queryBuilder('destroy');
var queryBuilderConfig = {
filters: filters,
operators: operators,
rules: rules_basic
};
queryBuilder.queryBuilder(queryBuilderConfig);
$('#next-button').on('click', function() {
let result = $('#querybuilder').queryBuilder('getRules');
let res = $('#querybuilder').queryBuilder('getSQL', $(this).data('stmt'), false);
let resStringify = res.sql + (res.params ? '\n\n' + JSON.stringify(res.params, undefined, 2) : '')
if (!$.isEmptyObject(result)) {
$('#result').val(JSON.stringify(result, null, 2));
$('#result-sql').val(resStringify);
$('form#data_filter_form').submit();
}
});
});
</script>
Please or to participate in this conversation.