Operator alias (less_or_equal === before_or_on)

[kctdfh]

Thanks for the nice library! Very well organized and documented! Been a joy to work with!

Now, we want aliases for our date filter type's operators. For instance, instead of choosing "Less or Equal" when you want a before or on date, you'd select "Before or On".

I have done the following so far:

• Added the operator phrases in the language file.
• Added these new operators to defaults.js.
• Added unit tests for each operator in core.module.js.
• Added the new operators in sql-support.js in the sqlOperators object (internal to SQL conversion).

What I'm uncertain of is the SQL to internal conversion. The function that creates the SQL operator doesn't have access to filter.type (as far as I can tell) so I can say something like "turn both less_or_equal and before_or_on to <= ?" but can't say something like "turn <= ? to less_or_equal for anything but date fields, otherwise turn it to before_or_on".

Is there any way I could pass the filter data to the sql-support plugin?

p.s. we're not using mongodb-support and invert plugins.

Thanks

[mistic100]

You should not modify the source files to do that.

There is documentation to create your own operators https://querybuilder.js.org/index.html#operators and attach them to filters https://querybuilder.js.org/index.html#filters

and how to configure it for the sql plugin https://querybuilder.js.org/plugins.html#sql-support

---

tha fact is you must have two differents operators, one for the dates and one for other filter types, don't try to use the same and change it's label

[kctdfh]

Well, naturally I have read the docs dozens of times but I think you're misunderstanding my intent.

Let's say I create my own operator in the same way as your link suggests. There is no property for an SQL symbol in that operator object. The SQL Import docs also mentions nothing about setting rules based on custom operators. I know I can pass a SQL string with params but the function doesn't know the filter type. So I have to modify the source.

I'm simply trying to call a custom operator (that has the same SQL operator symbol as an included one) by a different name based on filter type. I have it working for internal to SQL but SQL to internal doesn't work because setRulesFromSQL doesn't know the filter type.

Let's say I have a date filter type for some purchaseDate filter ID that looks like this:

{ id: 'purchaseDate', label: 'Purchased Date', type: 'date', input: 'text', operators: 'before_or_on', }

I have this operator pre-defined earlier in the options object as:

{ type: 'before_or_on', nb_inputs: 1, multiple: false, apply_to: ['datetime'] }

Since this is a new operator type, I have defined it in the lang object. Since to SQL, it's the same as 'less_or _equal', I have also added the following line in sql-support.js inside of the sqlOperators object:

before_or_on: { op: '<= ?' }

Now when I get my builder's SQL, I have a correct string.

However, if I want to set my builder's rules based on a SQL string, I won't be able to because setRulesFromSQL thinks my rule is 'less_or _equal' while my filter does not allow for that operator. The builder renders and my operator field is empty (value is null). This is because the setRulesFromSQL function is designed to turn <= into 'less_or _equal'. It can't check for the available operators and see if there's anything else that is available for the rule and has a SQL operator that matches with what it's trying to load in.

I hope I have been clear. Maybe this is a feature request? We have no way of asking: does this operator exist for this field ID? If not, check to see if any other operator types available have the same SQL operator and select that one instead.

Hope I was clear! Cheers!

[mistic100]

Not tested but it should work

$('#builder').queryBuilder({
    operators      : [
        { type: 'less_or_equal' },
        { type: 'before_or_on', nb_inputs: 1, multiple: false, apply_to: ['datetime'] }
    ],
    filters        : [
        { id: 'quantity', label: 'Quantity', type: 'integer', operators: ['less_or_equal'] },
        { id: 'purchaseDate', label: 'Purchased Date', type: 'date', input: 'text', operators: ['before_or_on'] }
    ],
    sqlOperators   : {
        before_or_on: { op: '<= ?' },
    },
    lang: {
        operators: {
            before_or_on: 'Before or on',
        }
    }
})
    .on('sqlToRule.queryBuilder.filter', (e) => {
        if (e.value.id === 'purchaseDate' && e.value.operator === 'less_or_equal') {
            e.value.operator = 'before_or_on';
        }
    });

[kctdfh]

Bless you for this! The event put me on the right path.

I ended up making a hacky solution that works for my specific case. Basically since I made a plugin that extends the defaults (trying to reduce the size of the options object since we have a specific use-case), I added two public methods called getSQLop and getApplyTo that do what they're called. I call both on e.value.operator. I then call getSQLop on each member of the apply_to array and add them all to an array alongside getSQLop of e.value.operator. If this array has a repeated element, I know that I need to change the rule's operator. I then make an array of all the operator types that have this repeated SQL operator. In my case, no operator is repeated more than twice so this array is always going to have 2 members. If one of them is the same as e.value.operator, I set e.value.operator to the other one.

Could be a useful plugin but my solution only works if you have 2 and only 2 default operators with the same SQL operator. It could be easier if the sqlToRule.queryBuilder.filter passed the filter type but I don't think it's possible since the SQL string is not going to contain this information.