Backward compatibility from sql

[jowher]

Is your feature request related to a problem? Please describe.
Can we populate the query builder from the sql (backward compatibility), If I provide the WHERE clause logic can we populate the query builder? That'd be a great feature to have.

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

[ukrbublik]

Import from SQL is supported with package @react-awesome-query-builder/sql
https://github.com/ukrbublik/react-awesome-query-builder/tree/master/packages/sql

[jowher]

@ukrbublik Thanks for this, I noticed one issue though, we have some custom operators like IN,
when I try to import a query with IN it is throwing error: ["Can't convert operator IN"]
Is there a workaround to handle this?

[ukrbublik]

What's your SQL that gives error?
In demo app you can import query like color IN ('green', 'yellow')

[jowher]

tried this prcdr_cd IN ('95911','95886','95816','95913','95912','95910','94690') AND adjudication_dt < '2025-03-11'"
and it is erroring, the date filter loads properly

[jowher]

@ukrbublik Could it be because we are overriding certain operators : in, like, not_like etc,

I noticed for the operators that is overridden, builder not loading properly from query

[ukrbublik]

For import you need to override sqlImort like

react-awesome-query-builder/packages/core/modules/config/index.js

Line 172 in dfb464c

sqlImport: function (sqlObj, _, sqlDialect) {

For export - sqlOp or sqlFormatOp:

react-awesome-query-builder/packages/core/modules/config/index.js

Line 470 in dfb464c

sqlFormatOp: (field, op, values, valueSrc, valueType, opDef, operatorOptions, fieldDef) => {

[jowher]

@ukrbublik I added as you mentioned :

 select_any_in: {
    label: 'Any in',
    sqlFormatOp: (field, _, values, valueSrc) => {
      if (valueSrc === 'value') {
        const coalesceField = `COALESCE(${field}, '')`
        return `${coalesceField} IN (${formatInputQuoteSelect(
          values,
        )})`.toUpperCase()
      } else return undefined
    },
    sqlImport: function (sqlObj: SqlObject, _: any, sqlDialect: string) {
      if (sqlObj?.operator === "IN" || sqlObj?.operator === "NOT IN") {
        const not = sqlObj?.operator === "NOT IN";
        
        const [fieldObj, valuesObj] = sqlObj.children || [];
    
        console.log("Processing sqlObj:", sqlObj);
        console.log("Operator:", sqlObj.operator);
        
        if (valuesObj && valuesObj.children && Array.isArray(valuesObj.children)) {
          const values = valuesObj.children.map(child => child.value);
          console.log("Original values for IN clause:", values);
    
          values.forEach(value => {
            console.log("Processing value:", value);
          });
    
          sqlObj.operator = not ? "not_in" : "in";
          console.log("Updated sqlObj after processing:", sqlObj);
          return sqlObj;
        } else {
          console.warn("Expected an array for IN clause values, but got:", valuesObj);
        }
      }
    },
    valueTypes: ["multiselect"],
  },

It is printing the sqlObj properly like the following but not loading to the widget

 {
    "not": false,
    "children": [
        {
            "field": "application_cd"
        },
        {
            "children": [
                {
                    "valueType": "single_quote_string",
                    "value": "C25"
                },
                {
                    "valueType": "single_quote_string",
                    "value": "ACS"
                }
            ],
            "not": false,
            "_type": "expr_list",
            "oneValueType": "single_quote_string",
            "values": [
                "C25",
                "ACS"
            ]
        }
    ],
    "operator": "in"
}

[jowher]

Also we have a custom IN operator or text widget (where we can pass comma separated list in textfield which we will process and create IN operator logic splitting on commas), in that case with the same sqlImport logic it is rendering in the ui but showing an error

[jowher]

@ukrbublik In the code you pointed, I don't see any override added for in operator

[jowher]

when I add to load this query application_cd IN ('ACS', 'C25') AND adjudication_dt < '2025-03-12', it is only loading the date to the builder but application_cd is not there in the tree created,
I see a warning

[
    {
        "path": [
            "b9989a8b-4567-489a-bcde-f1958d32eb97",
            "9aabbaba-4567-489a-bcde-f1958d34e101"
        ],
        "errors": [
            {
                "key": "INCOMPLETE_LHS",
                "args": {},
                "side": "lhs",
                "fixed": true,
                "str": "Incomplete LHS"
            }
        ],
        "itemStr": "?",
        "itemPosition": {
            "caseNo": null,
            "globalNoByType": 1,
            "indexPath": [
                1
            ],
            "globalLeafNo": 1,
            "index": 2,
            "type": "rule",
            "isDeleted": true
        },
        "itemPositionStr": "Deleted leaf #2 (index path: 2)"
    }
]

I override my 'select_any_in' operator as I provided in the previous comment and that is consoling the sqlObj as in the previous comment, but for some reason it is not setting properly to the tree with the above warning