How can I add an custom operator in Query Builder?

Question

Hi, how can I add an custom operator in Query Builder?

When selecting 'DB Field Join', the Value Input displays DropdownTree(Table2),

SQL Output:Employee.ID = Table2.ID

When selecting other rules, display them by default.

Thanks.

Image_2434_1723533474340

Thanks.

Keerthikaran Venkatachalam · Answer

Hi lixin,Thank you
for reaching out to Syncfusion Support.We have checked the reported query and prepared a
sample to meet your requirements. By utilizing the operators
property of the QueryBuilder column, you can add a custom operator.
Additionally, with the value
template feature of the QueryBuilder, you can render a DropDownTree
component in the value field. Please refer to the code snippets and samples
below.

let columns: ColumnsModel[] =
  [
          {field: 'Employee', label:
  'Employee', 
          columns: [
              { field: 'ID', label: 'ID', type:
  'number',  operators: 
              [ 
              { value: 'equal', key:'Equal' },
              { value: 'notequal', key:'Not
  Equal' },
              { value: 'DB Field Join', key: 'DB Field Join',
  sqlOperator: "=" }
              ],
              template: {
                  write: (args: { elements:
  Element, values: string[] | string, operator: string }) => {
                      if (args.operator == "DB Field Join")
  {
                          let dropDownTreeObject:
  DropDownTree = new DropDownTree({
                             
  //binding data source through fields property
                             
  fields: { dataSource: data, 
  value: 'field', text: 'label', child: 'columns', expanded: 'expanded',
  selectable: 'selectable' },
                             
  treeSettings: { expandOn: 'Click' },
                             
  showClearButton: false,
                             
  changeOnBlur: false,
                             
  cssClass: 'e-qb-ddt',
                              //
  set placeholder to Dropdown Tree input element
                             
  placeholder: "Select a Item",
                             
  change: (e: any) => {
                                 
  (dropDownTreeObject.element as any).value = e.value[0];
                                 
  qryBldrObj.notifyChange(e.value[0], e.element);
                              }
                          });
                         
  dropDownTreeObject.appendTo('#' + args.elements.id);
                      } else {
                          let numeric: NumericTextBox =
  new NumericTextBox({
                             
  min: 0, 
                             
  max: Number.MAX_VALUE,
                             
  format: 'n',
                             
  value: 0,
                             
  change: (e: any) => {
                                 
  qryBldrObj.notifyChange(e.value, e.event.target);
                              }
                          });
                          numeric.appendTo('#'
  + args.elements.id);
                      }
                      
                  }
              },
              },
              { field: 'DOB', label: 'Date of
  birth', type: 'date'},
              { field: 'HireDate', label: 'Hire
  Date', type: 'date'},
              { field: 'Salary', label:
  'Salary', type: 'number'},
              { field: 'Age', label: 'Age',
  type: 'number'},
              { field: 'Title', label: 'Title',
  type: 'string'}
          ]
          },
      ]

Sample link: https://stackblitz.com/edit/5hkkty-kwvxba?file=index.ts
Please let
us know if you need any further assistance on this.Regards,KeerthiKaran
K V

lixin · Answer

Thanks for your reply, it solved my problem, but I still have a question.

When the type of field.id is set to string and the rule is selected as DB Field Join, the SQL output by the QueryBuilder.getSqlFromRules() method is Employee.ID = 'Employee.DOB'.

Can it be output as [Employee.ID = Employee.DOB] ?

Image_7253_1723730388793

There is another question. When using the DropDownTree with the same settings, the content displayed after selection is different.
Image_3908_1723730415869

Thank you very much.

Keerthikaran Venkatachalam · Answer

Hi lixin,Query 1: When the type
of field.id is set to string and the rule is selected as DB Field Join, the SQL
output by the QueryBuilder.getSqlFromRules() method is Employee.ID =
'Employee.DOB'. Can it be output as [Employee.ID = Employee.DOB] ?We have checked the reported query, and currently we do
not have support for retrieving SQL rule values without string quotes.However,
we have considered your requirement an improvement “Provide the option to
get a SQL rule value without string quotes using the custom rule property"
and logged an improvement report for it. You can track the status of this
improvement by using the following feedback report link.Feedback: https://www.syncfusion.com/feedback/60502/provide-the-option-to-get-a-sql-rule-value-without-string-quotes-using-the-custom We
regretfully can't start this improvement implementation immediately since we
prioritized bug fixes and features that came before them in the queue for
forthcoming releases. The time between releases is typically at least three
months. Every release cycle, we assess all open features again during the
planning phase and decide which ones to deploy based on a number of criteria,
such as the product vision, technology viability, and user interest. The
feedback will be moved to scheduled status with an estimated delivery period
whenever we have more information to provide regarding the development of these
improvements. We appreciate your patience until then.Query 2: There is
another question. When using the DropDownTree with the same settings, the
content displayed after selection is different.To render the DropDownTree component with the same
settings as the QueryBuilder value template, please refer to the below code
snippets and samples.

<input
  id="default" />
  ….
  let treeObj:
  DropDownTree = new DropDownTree({
          //binding data source through fields
  property
          fields: { dataSource: data,  value: 'field', text: 'label', child:
  'columns', expanded: 'expanded', selectable: 'selectable' },
          treeSettings: { expandOn: 'Click' },
          showClearButton: false,
          changeOnBlur: false,
          cssClass: 'e-qb-ddt',
          // set placeholder to Dropdown Tree
  input element
          placeholder: "Select a
  Item",
          change: (e: any) => {
              debugger
              (treeObj.element as any).value =
  e.value[0];
          }
      });
      treeObj.appendTo('#default');

Sample link: https://stackblitz.com/edit/5hkkty-nxaull?file=index.ts Please let us know if you need any further assistance on
this.Regards,KeerthiKaran K V