HTML Elements Forums General Discussions QueryBuilder value to SQL or MongoDB

This topic contains 3 replies, has 2 voices, and was last updated by Avatar Hristofor 2 weeks, 1 day ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #101096
    Avatar
    tanner anz
    Participant

    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?

    #101097
    Avatar
    Hristofor
    Participant

    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

    #101098
    Avatar
    tanner anz
    Participant

    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?

    #101099
    Avatar
    Hristofor
    Participant

    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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.