jQuery-QueryBuilder
jQuery-QueryBuilder copied to clipboard
mistic100
[feature request] Compare to other column
This repo is fantastic!
My application's database fetch operations have a very common (to my app) use case where the user wants to compare two fields, usually as "FIELD1 < FIELD2 OR FIELD1 > FIELD3"
I'd like to add a plugin adding additional 'field comparison' operators (one for each of the tradition operators, but specifying a field comparison rather that comparison to an explicit value) that, when one of these operators is selected, will trigger a rule's value to render an additional column/field selection, so that the two columns can be compared by the users selected operator.
I'd also like to integrate with the SQL parser so that queries containing column to column comparisons can be parsed by the SQL parser and rendered as rules in the SQL builder.
I know I need to perform some operations on the afterUpdateRuleOperator.queryBuilder event, but not sure where to go from there.
I can't really explain what is needed to achieve this.
Please tell me if you got any progress, I wouldn't duplicate the work if I got the time to do it myself.
Thanks for the response. I haven't made any progress on this. But I will certainly share if I manage to figure it out. I am hoping this is can be implemented as a custom field 'input' that simply renders the field list again as a select input but with an appropriate input name.
I just started playing with querybuilder yesterday and I was also wondering about comparing to other fields. I don't think I need it yet, but thought I'd mention my interest as well.
I had a similar need whilst putting together an htmlwidget for use in R/Shiny. I have a hacky solution that changes the rule-value-container depending on the operator. There's an example at http://codepen.io/harveyl888/pen/mOejgN. Like I said - it's particularly hacky and I'm sure there's a much better way to do this using plugins but this seems to serve my purpose for the moment.
I tried : it's extremely complicated to integrate this in a generic way. The number of side effects is outstanding.
Any solution for this ? Comparing two fields. Even I want to do FIELD1 < FIELD2 OR FIELD1 > FIELD3
It will be of great help, if you can add the Column Comparison feature if your next release. Waiting for this feature.
Got really excited about finding this project. Looks awesome. But unfortunately, this a show-stopper for me.
I tried : it's extremely complicated to integrate this in a generic way. The number of side effects is outstanding.
Can you elaborate what the "generic way" means? i.e. What is your conception of how this feature need to be added? In my opinion there are several possibles solution and sure you have a preference. This should help others to get an idea of what could be an acceptable solution here and what probably will not be acceptable.
I ended writing a new plugin to alternate between values and variables. Please note that i'm using selectize with a custom plugin also and I wrote another jQuery-QueryBuilder plugin to use selectize in the rule filter.
So, a more general plugin could be required to work with the default features of the jQuery-QueryBuilder, but this is a general idea. I use the data property to distinguish between when we use values and when we use variables. Please see this commit for more info.
Here ia what i wrote. Please note that this plugin add a new rule button to swap between variables and values and this is more less what is doing the plugin filter-description:
/**
* @class VariableValues
* @memberof module:plugins
* @description Make possible the usage of variables as values.
*/
QueryBuilder.extend({
setFilter: function(filter) {
var self = this;
filters = this.change('setFilters', this.filters);
for (var pos in this.filters) {
if (this.filters[pos].id == filter.id) {
this.filters[pos] = filter;
this.trigger('afterSetFilters', this.filters);
return true;
}
}
return false;
},
getFilters: function() {
return this.filters;
},
getPlugins: function() {
return this.settings.plugins;
}
});
QueryBuilder.define('variable-values', function(options) {
var Selectors = QueryBuilder.constructor.selectors;
this.on('afterCreateRuleOperators', function(e, rule) {
var builder = e.builder;
var $b = rule.$el.find('button.variable-values');
if ($b.length === 0) {
$b = $('<button type="button" class="btn btn-xs btn-primary variable-values variable-values-values"><i class="' + options.icon + '"></i> Variables</button>');
$b.prependTo(rule.$el.find(Selectors.rule_actions));
if (rule.filter.realFilter) {
var newFilter = rule.filter.realFilter;
if (builder.setFilter(newFilter)) {
var plugins = builder.getPlugins();
if ((plugins) && (plugins['selectize'])) {
rule.$el.find('.rule-filter-container select')[0].selectize.setValue(newFilter.id);
} else {
rule.$el.find('.rule-filter-container select').val(newFilter.id);
}
}
rule.filter.realFilter = null;
}
$b.on('click', function(e) {
var filter = rule.filter;
if (!filter.realFilter) {
var filters = builder.getFilters();
var values = [];
for (var pos in filters) {
values.push({"value":pos, "label":filters[pos].id, "optgroup":null});
}
var newFilter = {};
newFilter.id = filter.id;
newFilter.label = filter.label;
newFilter.description = filter.description;
newFilter.type = filter.type;
newFilter.input = "select";
newFilter.values = values;
newFilter.operators = ["equal","not_equal","is_null","is_not_null"];
newFilter.unique = false;
newFilter.colors = {};
newFilter.validation = "";
newFilter.placeholder = "";
//FIXME: We need to use 'if (options.plugin = "selectize")' to really construct the corresponding widget.
newFilter.plugin = "selectize";
newFilter.plugin_config = {
"valueField":"id","labelField":"name","searchField":"name","sortField":"name",
"create":true,"maxItems":1,"initUnselected":true,"plugins":["typing_mode","remove_button"]
};
newFilter.field = filter.field;
newFilter.data = {"variables": true};
newFilter.optgroup = null;
newFilter.has_optgroup = false;
newFilter.realFilter = filter;
if (builder.setFilter(newFilter)) {
var plugins = builder.getPlugins();
if ((plugins) && (plugins['selectize'])) {
rule.$el.find('.rule-filter-container select')[0].selectize.setValue(newFilter.id);
} else {
rule.$el.find('.rule-filter-container select').val(newFilter.id);
}
}
$b.removeClass("variable-values-values");
$b.html('<i class="' + options.icon + '"></i> Valores');
} else {
$b.addClass("variable-values-values");
$b.html('<i class="' + options.icon + '"></i> Variables');
var newFilter = filter.realFilter;
if (builder.setFilter(newFilter)) {
var plugins = builder.getPlugins();
if ((plugins) && (plugins['selectize'])) {
rule.$el.find('.rule-filter-container select')[0].selectize.setValue(newFilter.id);
} else {
rule.$el.find('.rule-filter-container select').val(newFilter.id);
}
}
filter.realFilter = null;
}
});
} else {
$b.css('display', '');
}
});
}, {
"icon": 'glyphicon glyphicon-transfer',
"plugin": "selectize",
"plugin_config": {
"valueField":"id","labelField":"name","searchField":"name","sortField":"name",
"create":true,"maxItems":1,"initUnselected":true,"plugins":["typing_mode","remove_button"]
},
});
You can feel free to improved this idea and use it how you want, If you think it have the right direction.