Complex Binding : IS NULL and Grouping is broken when using setRulesFromSql

[Chris Persichetti]

Hi,

I ran into an issue that appears to be related to the IS NULL (maybe others) that when rules are set using SQL, it doesn't group correctly.

How to re-create (using the Complex binding example as a guide) taking out most of the code and going with one column setup but with a seperator.

Column Setup:

    this.columnsStatic = [  

                {field: 'Name1', label: 'Name1', type: 'string'},

                {field: 'Name2', label: 'Name2', type: 'string'},

                {field: 'Name3', label: 'Name3', type: 'string'},

                {field: 'Name4', label: 'Name4', type: 'string'},

                {field: 'Name5', label: 'Name5', type: 'string'},

                {field: 'Name6', label: 'Name6', type: 'string'},

                {field: 'Name7', label: 'Name7', type: 'string'} ]

HTML:

<ejs-querybuilder id="querybuilder" #querybuilder width="100%" enableNotCondition = true  separator= '.' (ruleChange)="updateRule($event)" (change)="updateRule($event)">

    <e-columns>

    <e-column field="s" label="Static Value" [columns] = "columnsStatic"></e-column>

    </e-columns>

</ejs-querybuilder> 

  onSetSqlRules(e:Event): void {

    var sql = "(s.Name1 IS NULL AND s.Name2 IS NULL) AND (s.Name3 IS NULL AND s.Name4 IS NULL)";

    this.qryBldrObj.setRulesFromSql(sql); 

  }

  updateRule($event) : void {

    var sql = this.qryBldrObj.getSqlFromRules();

    console.log('sql');

    console.log(sql);

  }

To reproduce:

Click the set SQL Rules. You'll see instead of two groups there's a group within a group.

Change any of the name column drop downs to a different drop down. You then see in the column the sql that was s.Name1 IS NULL AND s.Name2 IS NULL) AND (s.Name3 IS NULL AND s.Name4 IS NULL)

is now something like:

(s.Name1 IS NULL AND s.Name5 IS NULL AND (s.Name3 IS NULL AND s.Name4 IS NULL))

Please take a look.

Thanks,

[Chris Persichetti]

FYI Also an issue with the basic setup and also an issue using IS EMPTY

import { Component, ViewChild } from '@angular/core';

import { QueryBuilderComponent } from '@syncfusion/ej2-angular-querybuilder';

@Component({

  selector: 'app-root',

  template: `<!-- To render Query Builder. -->

               <ejs-querybuilder #querybuilder width="70%" separator= '.'>>

                <e-columns>

                  <e-column field="EmployeeID" label="Employee ID" type="number"></e-column>

                  <e-column field="FirstName" label="First Name" type="string"></e-column>

                  <e-column field="TitleOfCourtesy" label="Title Of Courtesy" type="boolean" [values]="values"></e-column>

                  <e-column field="Title" label="Title" type="string"></e-column>

                  <e-column field="HireDate" label="Hire Date" type="date" format="dd/MM/yyyy"></e-column>

                  <e-column field="Country" label="Country" type="string"></e-column>

                  <e-column field="City" label="City" type="string"></e-column>

                </e-columns>

              </ejs-querybuilder>

              <button class="e-btn e-primary e-qb-button" (click)="setSql()" >Set Rules From SQL</button>

              <button class="e-btn e-primary e-qb-button" (click)="getSql()" >Get SQL From Rules</button>`

})

export class AppComponent {

  @ViewChild('querybuilder')

  public qryBldrObj: QueryBuilderComponent;

  title = 'my-app';

  values = ['true', 'false'];

  setSql(): void {

    this.qryBldrObj.setRulesFromSql("(FirstName IS NULL AND Title IS NULL) OR (FirstName IS NULL AND City IS NULL) ");

  }

  getSql(): void {

    console.log("sql: " + this.qryBldrObj.getSqlFromRules());

  }

}

The getSql() all gets the following after setting the rules via setRulesFromSql

(FirstName IS NULL OR Title IS NULL OR (FirstName IS NULL AND City IS NULL))

Thanks,

Chris

[Gayathri KarunaiAnandam]

Hi Chris, 

We have checked your reported query. We can reproduce your reported issue in our end. So, we have logged this issue as a bug, and it will be available in our upcoming patch release which will be scheduled on October 20^th,2021 and we appreciate your patience until then.   

You can also track the status of this bug by using our feedback portal link below.     

     

Feedback: https://www.syncfusion.com/feedback/29231/complex-data-binding-boolean-issue 

   

Please get back to us, if you need further assistance.   

Regards,   

Gayathri K   

[Chris Persichetti]

Great thanks.

Please make sure IS EMPTY works also.

Thanks,

Chris

[Chris Persichetti]

Also I want to make sure that this is a different issue than https://www.syncfusion.com/forums/169401/complexbinding-boolean-issue

I just noticed your link above to track status says "complex-data-binding-boolean-issue" (I clicked, but don't have access).  Just want to make sure this thread is a different issue than the boolean issue.

Thanks,

Chris

[Gayathri KarunaiAnandam]

Hi Chris, 

Query 1: Please make sure IS EMPTY works also. 

We will include is empty case while fixing this issue. As, promised this will be available in our October 20^th patch release. 

Query 2: I just noticed your link above to track status says "complex-data-binding-boolean-issue" (I clicked, but don't have access).  Just want to make sure this thread is a different issue than the boolean issue. 

We would like to let you know that please log out your account and login and try to access the feedback link. 

Please get back to us, if you need further assistance. 

Regards, 

Gayathri K 

[Gayathri KarunaiAnandam]

Hi Chris, 

Thank you for your patience.  

  

We are glad to announce that our weekly patch release (19.3.45) is rolled out. We have included the fix for your issue [Complex data Binding issue] in this release. So, we suggest you upgrade our Syncfusion packages to our latest version to resolve this issue in your end. (19.3.45). 

Package Link:  

https://www.npmjs.com/package/@syncfusion/ej2-angular-querybuilder  

  

Feedback Link: https://www.syncfusion.com/feedback/29231/issue-with-boolean-type-values-in-complex-data-binding 

Please let us know, if you need further assistance. 

Regards, 

Gayathri K