mistic100 / jquery-querybuilder Goto Github PK

It would be AMAZING if we could set rules in the gui from the json it generates with the getRules method

hi mistic100,

great code !!!
is there a chance to add functionality in order to get valid sql where clause out of json string?

For #22 and language files

Add build command line options to choose loaded modules and default language

I created a structure like
And
Category
Or
in_stock
category
AND
category
Name

With the structure it should have been
( Category and ( in stock || category || ( category) ) and name

However as you can see below i can not see how you can determine the end of a group. So i can not tell in the servlet that name belongs to the first group mapping and not the second.

between builder_rule_4 and builder_rule_3 there does not seem to be a way to tell.

Am i missing something obvious?

test.html?builder_group_0_cond=AND&builder_rule_0_filter=category&builder_rule_0_operator=equal&builder_rule_0_value=1&

builder_group_1_cond=OR&builder_rule_1_filter=in_stock&builder_rule_1_operator=equal&builder_rule_1_value=1&

builder_rule_2_filter=category&builder_rule_2_operator=equal&builder_rule_2_value=1

&builder_group_2_cond=AND&

builder_rule_4_filter=name&builder_rule_4_operator=equal&builder_rule_4_value=jared&

builder_rule_3_filter=price&builder_rule_3_operator=equal&builder_rule_3_value=45&result=

{type: 'not_ends_with', accept_values: true, apply_to: ['string'], sql: { op: 'NOTLIKE(?)',

needs to be
{type: 'not_ends_with', accept_values: true, apply_to: ['string'], sql: { op: 'NOT LIKE(?)',

I did a basic sql where price is less than equal to 100 and category is null and did a getSQL for prepared statement with ? placeholders. The sql is correct with category IS NULL but the values array for question marks is wrong. The array should have only one value for price, but it is adding a value null for category too which is wrong. If you try to build sql for 3-5 fields also where one of the condition in between is for null check it would still give wrong values array. The values array length should be equal to number of question marks.

price <= ? AND
(
category IS NULL
)

[
100,
null
]

I noticed the support to export rules as SQL has recently been added. In a project of mine, I convert rules to ElasticSearch filters which maps extremely well. Would there be interest in having this included here?

need this type of query. how can we get it from this builder.

name = 'john' OR name = 'mike'
AND
(
category IN (1)
)

there is no way we can put and between groups. how we can achieve this.

thanks.

When I run your system I get code like "field==0" however my db does not accept that as a valid part of the sql. Any suggestions ?

The tag allows you to nest dropdown items under an unclickable label.

Example

<select>
  <optgroup label="jQuery QueryBuilder v1">
    <option value="p1">prashker request</option>
    <option value="p2">is so cool</option>
  </optgroup>
</select>

Would be nice to have something like this, for more verbose dropdowns. Not sure yet of a proper implementation in terms of how to tell queryBuilder to do this...maybe in the filter[] as opposed to being flat, have some hierarchy that takes into account optgroup.

hi

thanks for your work

i tried to use jui_filter_rules and had some problems maybe your project could solve

is possible to add a rule that has 2 or 3 chained selects and pass all selected values or last select value ?

example rule
select 1 (ajax request) ->
select 2 (populated from 1 results, ajax request) ->
select 3 (populated from 2 result) needed value

i can write my own ajax requests i just need a example on how to integrate with your script

Hey - this is brilliant work - great job.

If you add type="button" to your buttons - it prevents them causing trouble if it happens to be in a form.

Also it would be great to expose the templates in the defaults - I am running Bootstrap 2.3.2 on a project here, and I couldn't see an easy way to tweak the style.

Thanks again,

Ian

I need to have only one condition, i.e. just OR.

As the QueryBuilder.DEFAULTS.conditions it's an array with two elements and you are using $.extend which replace arrays items instead of replacing all the array (http://bugs.jquery.com/ticket/9477) I didn't find a way to do it. Any suggestion ?

I have implemented a time select.
And I have the next values
"values": {
"09": "09:00",
"10": "10:00",
"11": "11:00",
"12": "12:00",
"13": "13:00",
"14": "14:00",
"15": "15:00",
"16": "16:00",
"17": "17:00",
"18": "18:00",
"23": "23:00"
}
When I refresh the page the hour 09:00 is positioned at the bottom of the select box. It is the last option. Can I stop this sorting action somehow?

Thanks

In addition to the "readonly" option in setRules, how about something like a "no-delete" option, which prevents the user from deleting the rule, but allows them to change and set the values?

Can you add a file with the following content named composer.json for your project?

{
    "name": "mistic100/jQuery-QueryBuilder",
    "description": "jQuery plugin for user friendly query/filter creator",
    "keywords": [ "jquery", "query", "builder", "filter"],
    "homepage": "https://github.com/mistic100/jQuery-QueryBuilder",
    "authors": [
        {
            "name": "Damien \"Mistic\" Sorel",
            "homepage": "http://www.strangeplanet.fr"
        },
    ],
    "support": {
        "issues": "https://github.com/mistic100/jQuery-QueryBuilder/issues"
    },
    "author": "Damien \"Mistic\" Sorel",
    "license": "MIT"
}

Hi, I was playing with your plugin last night and it seems perfect for the purpose of exposing features of a powerful search engine (syntax). Thank you for sharing your work!

I also think your plugin will be in need of more work (which I may contribute, at least to what I can). Here are the things that I noticed

window.QueryBuilder - the global object, is it really necessary? We are using AMD
bower.json - it is now exporting only the minified versions; for development it is good to export both versions
missing tests - I'd like to contribute tests written in mocha
problems in Chrome on Mac, version 35.0.1916.114. (and more broadly: how difficult will it be to support all major browsers?)

So, would you say all these things seem reasonable and worth including (pull requests)?

Thanks, roman

Hi. I've confirmed this behavior on the main demo site (demo-basic.html): the raw SQL output button doesn't seem to work for the empty and null options.

If you select "Name", then "equal", and type a string, then you can choose "JSON", "SQL (prepared statement)", or "SQL (raw)", and get the desired output.

However, if you choose "Name", then "is empty", only the first two options work. Clicking on "SQL (raw)" does nothing visible; if there is previous output showing, the output div doesn't change, and if there was no output showing, no output div appears.

The error in my JS console is "TypeError: a is null", from query-builder.min.js. I wish I could help more, but I'm not a JS person (which is why I was hoping to get this great functionality from a drop-in library!).

I reproduced the problem in both Firefox and Chrome, in case it was browser-dependent in some weird way. Thanks, and let me know if I can provide any additional useful information.

Your website https://mistic100.github.io/jQuery-QueryBuilder/ doesn't handle https very well, as there are quite a few resources which refer specifically to their http versions. Firefox (and some other browsers) block mixed content, making this site useless on HTTPS.

Thanks!

add option to disable groups creation

Maybe it is intended and I have to fix it in my system or maybe it is not.

When i create a SQL and want to select " NOT IN (1,2) "
the database shows all items except 1, 2 and NULL

example "not in 1,2"

NULL
1
2
3

Shows 3..... and I want it to show NULL, and 3

Hi
I'd like to use a conditions: ['AND'] option in cases where I only require AND logic but it appears this is not possible.
I can remove entirely using conditions: false and add extra conditions using conditions: ['AND','OR','XOR'] for example, but just displaying one or the other seems impossible.

I'm guessing this is to do with the way you are merging the custom option with the default value.

Great plugin by the way.

It could be a nice feature introducing "between" operator. Its behaviour would be similar to demo widget but with a preposition to link the couple of fields.
Obviously this could be done by adding a group and using two times the same filter with different operators, but it could be faster with such an operator.

Hi,

I'm unable to obtain something like

category IN (1) AND name ='aaa' OR (price = 3 AND name= 'bbb')

because the first operator is used also with the first group of rules
so you would only get

category IN (1) AND name ='aaa' AND (price = 3 AND name= 'bbb')
or
category IN (1) OR name ='aaa' OR (price = 3 AND name= 'bbb').

For this to be really useful all the combinations should be possible

I am using this querybuilder inside a asp.net page. When i have it inside a

thanks

Firstly, great plugin. Thanks for your hard work.

Is it possible to make this plugin load as a read only mode?

I am using it on a project where certain rules are only editable by certain role types.

However when viewing a rule that they do not have correct permission to edit, the rule must be visible to them in the same graphical form they are used to seeing it in when they edit the rule.

How can i achieve this please?

Hello,

I think the drag and drop feature to move rules around is a great idea as it offers an easy way to edit the query if I change my mind. Although I discovered that it doesn't let me move a rule (or a group) to the first position of a group. I realise that the way around this is to move the item to the second position first and then move down the one which is holding the first position, however this is a bit of an extra hurdle. It would be nice to have the drag and drop working seamlessly with the first position as well.
I tested this in Chrome and Firefox.

It would be fantastic if there was a way (hook or otherwise) to wait until the ajax requests had finished on fields in a input function before setting the value.

Or if there is a way could you point me to it?

I think that more flexibility for the validation callback function is needed. For example i want to have a validation which also depends on the operator, this is quite hard to accomplish now. By adding the $rule and operator parameters to the callback function this would be much easier to accomplish.

Thanks

Hi @mistic100 ,
some methods like "setRules" triggers automatically the validation of the filters.
Put the case I have a form which, between other fields, has your query builder and uses a function to validate it all. I want to validate the entire form before submitting it, but there's only an event (onValidationError), there's no way for the validating function to know when filters are valid instead.
It could be usefull having an onValidationSuccess called when all filters setted are good or just let the validation not to be triggered by setRules but just by a standalone method.

New feature.

When adding a rule and setting a filter like
"Name - begins with - test"
I would bee good if that filter will not be available for new rules anymore.

Is there a variable that we can set which does this? If not it would be nice to have this.
A variable to set so that filter will be available for only one rule.

The first group doesn't need a and or or does it?

I'm just starting to configure QueryBuilder. It looks amazing so far, but maybe I'm missing some options.

Is there a native way to require a certain filter to be set? The only validation options I see are run on filters that have already been set, but not looking for non-set filters.

For example, I have a "name" filter that MUST be included in the query before submitting. My workaround right now is to perform a manual validation in after getRules is called, where I loop through the json.rules object and check for a certain ID filter. If it's not present, then I don't continue. But it would be great if I could just specify "required: true" in the filter definition, so validation would fail automatically.

Also, just to be sure I'm understanding the validation methods, QueryBuilder doesn't provide any native way to, for example, display an error string, right? By default, the filter that failed validation turns red, but it's up to me to decide if and how I want to display the actual error string or message?

The icons option is not working and i can't find a implementation for this in the source code.

Great project! Exactly what we are after. Thanks for sharing. Any particular reason not supporting IE 8 or 9?

romanchyla@dbf6922

Hi,

Is it possible show "And" and "Or" buttons as Radio (Preferred) or Drop Down buttons. Users are not sure of color coding (light blue vs dark blue), and not sure which one was selected.

Radio or Drop Down buttons would remove this confusion. May there could be config option to show the original buttons, or show in Radio or Drop Down buttons

Thanks

On the basic demo, make on of the two "Category" rule invalid, the validation error is triggered but the output JSON contains the first rule (valid) where it should be empty

Hi, we are new to the querybuilder but first of all it seems fantastic!
is there some example of autocompleters widget use in query builder?
our goal is to autocomplete the value list for the field used in the query .
For example we have a table with the string attribute "municipalities" that has more than thousand names.
we would like to start writing the first characters 's name and have an autocomplete of the field.

Thank you in advance
Manuela & Enrico

Is it possible to only allow so many sub-groups per level? i.e after the second sub-group is added, don't allow anymore?

When you use IN it would be logical to specify more than one argument (comma separated).
The SQL parser gives out a wrong result, though: if you specify IN a,b, the resulting query fragment reads as IN('a,b') which would search for a single argument instead of two.
I realize you may want to allow users to look for commas (but they could escape commas, couldn't they?), however a useful IN option should output more than one argument as IN('a','b','c'...)

Heres a little of what i'm attempting

$('#rulebuilder').queryBuilder({
        sortable: true,
        lang: {
            operators: {
                "equal": "is",
                "not_equal": "is not"
            }
        },

But they are not changing, or am i just misreading things?

Is it possible to have a Select input with a set of defined values, plus an option to add additional values like a regular Text input?

If it's not native, then I was thinking of having something like an "Add New Value" option in the Select's Values array, then prompting for the new value during validation. Or, maybe something similar is possible using onAfterSetValue?

Is there a simple way to move the add rule/group/delete buttons below the last rule in a group. I've tried doing it in the template and it puts all of them at the bottom of all rules.. Not sure the best way to proceed.

Thanks in advance for any advice.

Hello,

if i add a group to the query without filling out the new rule, getRules returns {}, even if there are other valid rules.

Instructions to reproduce in Basic-Demo:

• fill out the first rule
• only press "add group"-button (nothing else, means leave new rule empty)
• click on JSON-button (calls getRules)
  -> Now one can see in the output "{}".

Sadly in this case also no validation-error is reported (using onValidationError-callback).
The problem is now, that the query filters nothing. This is confusing for the user who builds the query.

I would expect a query-JSON that not contains this empty group.

Could you fix this, or is there a workaround to manually remove these empty groups?

Many Thanks in advance...

I'm building a rule builder and i have a rule that reads something like regex is ____ what i need is a way to make that read regex ____ is {dropdown of other rules} is the possible at all?

I tried to reduce operators by passing option on querybuilder creation, but it looks like due to deep jquery merge on passed in options and default options we can only add operators, not remove, https://github.com/mistic100/jQuery-QueryBuilder/blob/master/src/query-builder.js#L37 .

try passing something simple like:

operators : [
{type: 'equal', accept_values: true, apply_to: ['string', 'number', 'datetime']},
{type: 'not_equal', accept_values: true, apply_to: ['string', 'number', 'datetime']},
{type: 'in', accept_values: true, apply_to: []},
{type: 'not_in', accept_values: true, apply_to: []},
]

and notice it will not remove the existing options.

Current design entirely relies on DOM to access rules/groups data

Would be better to have Group and Rule objects which store the needed data and have a pointer to the DOM

Check visjs for a good design pattern https://github.com/almende/vis

Hi,
Is there an easy way to re-construct the sql json from the sql text ? For eg: I am using this library to construct rules and form a query text which i save in our data store. I want to be able to re-construct the filter rules given the text as input ? setRules expects the whole json to be passed and it would be convenient if we can have a way to reconstruct the filters from just plain text ?