Query Builder - setRulesFromSql issues with custom operators and templates

[KS]

Hello,

I have a few custom operators that I'm using with the Query Builder. One of them is for a Date field, where it sets the value to a specific string. For instance, the Next Month operator for the DueDate field would output "DueDate = '$(NEXT_MONTH)'". This works in the builder itself, but when trying to use setRulesFromSql with a SQL string that contains this snippet, the operator is getting set to Equals instead of the Next Month operator that it should be using.

Is there something that can be done to make sure the correct custom operator is selected when it reads in a SQL string?

Additionally, when I have a field that uses a template for creating a DropdownList control, I get an error when the destroy portion of the template executes while importing an SQL string that's supposed to use a dropdown value. Please see below for which line the error occurs and what the error is in the console:

This template has worked if I manually add the condition, but when getting rules from a sql string, it throws this error. Is this fixable as well?

Please note that these are urgent issues that we need to resolve ASAP.

Thank you,

KS

[Keerthikaran Venkatachalam]

Hi KS,

We are unable to replicate the issue on our end. We have prepared a sample and set the rule using the setRulesFromSql method of the QueryBuilder. It is working fine. I have attached the sample for reference.

Sample Link: https://stackblitz.com/edit/angular-ttxo1n-nv9dsb?file=src%2Fapp.component.ts

If you still facing issue, could you please share the issue's replicable sample or replicate the issue in our sample with replication steps and a video demonstration? Based on that, we will check and provide you with a better solution quickly.

Please let us know if you need any further assistance on this.

Regards,

KeerthiKaran K V

[KS]

Hello,

My question was about custom operators, which can be seen in the following sample:

https://stackblitz.com/edit/angular-ttxo1n-qc8m7n

I have an operator Next Month for the Date field that is supposed to set the SQL query to "Date = '$(NEXT_MONTH)'" when it is selected. However, when trying to load that string in through the setRulesFromSql method, it is not getting set properly; the operator shows Equals and a blank date value, when I do not want to show a value field for this operator.

I would expect the query builder to show the following after clicking the button to set the rules from the query:

Is this possible?

Thank you,

KS

[Saranya Dhayalan]

Hi Ks,

Checked your reported query, you can achieve this to setRules method in querybuilder component. Since, we need to change the sql string to proper rule model (change "=" to "$(NEXT_MONTH)).

    <ejs-querybuilder id="querybuilder" #querybuilder cssClass="row" [dataSource]="dataSource" [columns]="filter" [rule]="importRules"

    (change)="change($event)" (created)="created($event)" width="100%">

    </ejs-querybuilder>

  </div>

  <button class="e-btn e-primary e-qb-button" (click)="setRules()" >set Rules From Sql</button>

  <button class="e-btn e-primary e-qb-button" (click)="getRules()" >Get Sql</button>

  <button class="e-btn e-primary e-qb-button" (click)="reset()" >Reset</button>

 

Then you can get the sql from the rules by using getSqlfromRules method.

getRules() {

    this.sql = this.qryBldrObj.getSqlFromRules(this.qryBldrObj.rule);

  }

Then you can pass this sql getRulesFromSql method.   Then you pass use the rules in updateCustomR

ule method.

setRules(): void {

    if (this.sql !== '') {

      var rule = this.qryBldrObj.getRulesFromSql(this.sql);

      this.updateCustomRule(rule.rules);

      this.qryBldrObj.setRules(rule);

      this.hideValue();

    }

  }

In updateCustomRule method, you can iterate the rules and update the operator value. 

updateCustomRule(rules: RuleModel[]) {

    for (let i: number = 0; i < rules.length; i++) {

        if (rules[i].rules) {

          this.updateCustomRule(rules[i].rules);

        } else {

            if (rules[i].value === '$(NEXT_MONTH)') {

              rules[i].operator = '$(NEXT_MONTH)';

            }

        }

    } 

  }

You can hide the value dropdownlist by using the hideValue method in created event.

created() {

    this.hideValue();

  }

 

hideValue() {

    let ruleList: NodeListOf<HTMLElement> = this.qryBldrObj.element.querySelectorAll('.e-rule-container');

    for (let i: number = 0; i < ruleList.length; i++) {

      if ((ruleList[i].querySelector('.e-rule-operator input') as any).value === 'Next Month') {

        (ruleList[i].querySelector('.e-rule-value') as any).style.display = 'none';

      }

    }

  }

Please check the below sample link

https://stackblitz.com/edit/angular-ttxo1n-xz6nhv?file=src%2Fapp.component.ts

Check the above sample and get back to us if you need further assistance on this.

Regards,

Saranya D