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

[bhuvneshsharmaa]

@jowher I also had the same requirement which you are trying to solve. By default In/Not_In expects values to be selected from list(RHS) but It will not be applicable for string type containing comma separated values.
I tried to override default IN and custom spelLogic handling as well but somehow It didn't work. So I solved it by creating a custom operator which will work exactly like IN but you need to write simple formatting function for this like below.

any_in: {
				label: 'Any In',
				labelForFormat: 'Any In',
				reversedOp: 'not_in',
				spelOp: '${0}.any_in(${1})',
				sqlOp: 'in',
				valueSources: ['value'],
				valueTypes: ['text'],
				sqlFormatOp: (field: string, op: string, value: any) => {
					const formattedValues = value
						.split(',')
						.map((val: string) => {
							const cleanedVal = val.trim().replace(/^'|'$/g, '');
							return `'${cleanedVal}'`;
						})
						.join(',');
					return `${field} in (${formattedValues})`;
				},
			},
			not_in: {
				label: 'Not In',
				labelForFormat: 'Not In',
				spelOp: '${0}.not_in(${1})',
				reversedOp: 'any_in',
				sqlOp: 'not in',
				valueSources: ['value'],
				valueTypes: ['text'],
				sqlFormatOp: (field: string, op: string, value: any) => {
					const formattedValues = value
						.split(',')
						.map((val: string) => {
							const cleanedVal = val.trim().replace(/^'|'$/g, '');
							return `'${cleanedVal}'`;
						})
						.join(',');
					return `${field} not_in (${formattedValues})`;
				},
			},

and Once your rule is created while reverse parsing pass the query to below function and It will format the query to desired spelFormat.

export const formatQueryOperator = (query: string) => {
	const regex =
		/(\w+(?:\.\w+)*)\s+(contains|not_contains|in|not_in)\s+('[^']*'|\(\s*'[^']*'(?:\s*,\s*'[^']*')*\s*\))/g;
	return query.replace(regex, (match, field, operator, value) => {
		if (operator === 'in' || operator === 'not_in') {
			const values = value.match(/'[^']*'/g).map((v: any) => v.slice(1, -1));
			const mergedValues = values.join(',');
			const formattedOperator = operator === 'in' ? 'any_in' : 'not_in';
			return `${field}.${formattedOperator}('${mergedValues}')`;
		} else {
			return `${field}.${operator}(${value})`;
		}
	});
};

I hope this will help.

[bhuvneshsharmaa]

and also you need to define which type will support your new custom operator like this

types: {
			...InitialConfig.types,
			customText: {
				...MuiConfig.types.text,
			},
			text: {
				...InitialConfig.types.text,
				widgets: {
					...InitialConfig.types.text.widgets,
					field: {
						...InitialConfig.types.text.widgets.field,
						operators: [
							'equal',
							'not_equal',
							'contains',
							'not_contains',
							'starts_with',
							'ends_with',
						],
					},
					text: {
						...InitialConfig.types.text.widgets.text,
						operators: [
							...(InitialConfig.types.text.widgets.text.operators ?? []),
							'contains',
							'not_contains',
							'any_in',
							'not_in',
						],
					},
				},
			},
		},