QueryBuilder value to SQL or MongoDB

[tanneranz]

I’m demoing the QueryBuilder, but I’m finding the JSON object returned in the value doesn’t do me any good.
I’ve tried using the “getLinq()” function, but that is not working as well. It just returns a “” value.
Can you provide any suggestions for transforming this to SQL or MongoDB?

[Hristofor]

Hi tanner anz,
the getLinq() method only works when you’ve specified customOperators list. Without that it’s not possible for the element to recognize an expression. Here’s an example of a Smart.QueryBuilder settings object that accepts a Linq expressions from value and converts the current element value to Linq expression via the method getLinq method. The code also illustrates how to use custom operators for certain fields:

{
            allowDrag: true,
            //Defining the necessary custom operations
            customOperations: [
                {
                    label: 'Less than',
                    name: '<',
                    expressionTemplate: '{0} < {1}',
                    //Determines which arguments from an expression are used for the fieldName and value for the QueryBuilder.
                    //Used when converting a Linq expression to QuieryBuilder value.
                    expressionReaderCallback: function (expression, bindings) {
                        return { fieldName: bindings[0], value: (bindings[1] === '@minPrice' ? 0 : 100) };
                    }
                },
                {
                    label: 'Status Equals',
                    name: 'status_equals',
                    expressionTemplate: '{0} = "{1}"'
                },
                {
                    label: 'Starts With',
                    name: 'startswith',
                    expressionTemplate: '{0}.StartsWith("{1}")'
                },
                {
                    label: 'List Not Contains',
                    name: 'list_not_contains',
                    expressionTemplate: '{0}.Any(!it.Contains("{1}"))'
                },
                {
                    label: 'Operator Contains',
                    name: 'contains',
                    expressionTemplate: '{0}.Contains("{1}")'
                },
                {
                    label: 'Contains',
                    name: 'keyvalue_contains',
                    expressionTemplate: '{0}["{1}"].Contains("{2}")',
                    //Determines the arguments for the Logical statement. Used when converting value to Linq expression
                    expressionBuilderCallback: function (dataField, operation, objValue) {
                        return this.expressionTemplate.replace('{0}', dataField).replace('{1}', objValue.name).replace('{2}', objValue.occupation);
                    },
                    //Determines which arguments from the expression are used for the fieldName and value for the QueryBuilder.
                    //Used when converting a Linq expression to QuieryBuilder value.
                    expressionReaderCallback: function (expression, bindings) {
                        return { fieldName: bindings[0], value: { name: bindings[1], occupation: bindings[2] } };
                    },
                    //Determines the a custom editor tempalte
                    editorTemplate: function (fieldType, valueObj, fieldData) {
                        const editor1 = document.createElement('smart-input'), editor2 = document.createElement('smart-input'), label = document.createElement('label'), container = document.createElement('div');
                        container.className = 'container';
                        label.classList.add('custom-label');
                        label.textContent = 'is';
                        if (typeof valueObj !== 'boolean') {
                            editor1.value = valueObj.name;
                            editor2.value = valueObj.occupation;
                        }
                        container.appendChild(editor1);
                        container.appendChild(label);
                        container.appendChild(editor2);
                        return container;
                    },
                    //Determines the HTML representation of the editor's value
                    valueTemplate: function (editor, obj) {
                        return obj.name + ' is an ' + obj.occupation;
                    },
                    //Determines how the value of editor is handled by the QueryBuilder
                    handleValue: function (editor) {
                        const editors = editor.getElementsByTagName('smart-input');
                        return {
                            name: editors[0].value, occupation: editors[1].value
                        };
                    }
                },
                {
                    label: 'Relative Time',
                    name: 'relative_time',
                    expressionTemplate: '{0} <= "{1}"',
                    expressionBuilderCallback: function (dataField, operation, value) {
                        let days = Math.abs(new Date().getTime() - value.getTime()) / (1000 * 60 * 60 * 24), hours = Math.floor((days % 1) * 60), minutes = Math.round((hours % 1) * 60), seconds = Math.round((minutes % 1) * 60);
                        const format = (amount) => amount.toString().length < 2 ? '0' + amount : amount;
                        return this.expressionTemplate.replace('{0}', dataField).replace('{1}', format(Math.round(days)) + '.' + format(hours) + ':' + format(minutes) + '.' + format(seconds));
                    },
                    expressionReaderCallback: function (expression, bindings) {
                        let value = bindings[1], targetDate = new Date();
                        //Timespan type handling
                        if (/([0-9]{2}).([0-9]{2}):([0-9]{2}):([0-9]{2})/gm.test(value)) {
                            let timeSpan = /([0-9]{2}).([0-9]{2}):([0-9]{2}):([0-9]{2})/gm.exec(value);
                            targetDate.setDate(targetDate.getDate() + parseInt(timeSpan[1]));
                            targetDate.setHours(targetDate.getHours(), 0, 0, 0);
                            targetDate.setHours(targetDate.getHours() + parseInt(timeSpan[2]));
                            targetDate.setMinutes(targetDate.getMinutes() + parseInt(timeSpan[3]));
                            targetDate.setSeconds(targetDate.getSeconds() + parseInt(timeSpan[4]));
                        }
                        return { fieldName: bindings[0], value: targetDate };
                    }
                }
            ],
            fields: [
                { label: 'Program Name', dataField: 'programName', dataType: 'string', filterOperations: ['status_equals', 'startswith'] },
                { label: 'Started Within', dataField: 'startedWithin', dataType: 'dateTime', filterOperations: ['relative_time'] },
                { label: 'Part Number', dataField: 'partNumber', dataType: 'string', filterOperations: ['status_equals'] },
                { label: 'Operator', dataField: 'operatorObject', dataType: 'object', filterOperations: ['keyvalue_contains'] },
                { label: 'Operator', dataField: 'operatorString', dataType: 'string', filterOperations: ['contains'] },
                { label: 'Id', dataField: 'id', dataType: 'string', filterOperations: ['list_not_contains'] },
                { label: 'Unit Price', dataField: 'price', dataType: 'number', filterOperations: ['<', 'status_equals'] },
            ],
            showIcons: true,
            value: '(partNumber = "PN-5478" && programName = "Voltage Test") && (startedWithin <= "90.00:00:00" || operator.Contains("John"))'
        };

Also you can get the Linq expression on change event from event.detail.linq. You can use or convert the resulted string expression the way you need to.
Best Regards,
Christopher
Smart HTML Elements Team
https://www.htmlelements.com

[tanneranz]

Thank you for the detailed LINQ example. I highly suggest adding that to the demo section.
After looking at it though, the LINQ output is not what I’m looking for. In my initial post, I asked if you could suggest converting the value to SQL or MongoDB. Is this something that could be implemented? Maybe a “getSQL()” or “getMongo()” function?

[Hristofor]

Hi tanner anz,
thank you for the suggestion. We noticed that the demo is missing and it will be added to the website. The Smart.QueryBuilder has DynamicLinq support only. Any additional features require custom development. If you wish you can contact HTMLElements Sales for additional information.
Best Regards,
Christopher
Smart HTML Elements Team
https://www.htmlelements.com

[Author]

Posts