Cannot SetRulesFromSql if the SQL includes a term which is templated as a dropdown list.

[Darren]

The following code is based on the QueryBuilder sample code, but I have:

• Removed the "Rules" property
• Instead, attempted to SetRulesFromSql in the QueryBuilderCreated() event
  

It fails when the SQL contains a term which has a drop-down list in the querybuilder, with the exception in the comments:

@page "/qb"
@using Syncfusion.Blazor.QueryBuilder
@using Syncfusion.Blazor.DropDowns


<div class="col-lg-12 control-section">
    <SfQueryBuilder TValue="ExpenseData" Width="100%" DataSource="@DataSource" @ref="QueryBuilderRef">
        <QueryBuilderEvents TValue="ExpenseData" Created="QueryBuilderCreated"></QueryBuilderEvents>
        @* <QueryBuilderRule Condition="and" Rules="@Rules"></QueryBuilderRule> *@
        <QueryBuilderColumns>
            <QueryBuilderColumn Field="PaymentMode" Label="PaymentMode" Type=Syncfusion.Blazor.QueryBuilder.ColumnType.String>
                <QueryBuilderTemplates>
                    <ValueTemplate>
                        @{
                            string value = (string)context.Value;
                            if (value == "")
                            {
                                value = DefaultValue;
                            }
                        }
                        <SfDropDownList TValue="string" TItem="ItemFields" DataSource="@Items" @bind-Value="@value">
                            <DropDownListFieldSettings Text="Id" Value="Id"></DropDownListFieldSettings>
                            <DropDownListEvents TItem="ItemFields" TValue="string" ValueChange="e => OnChange(e, context)"></DropDownListEvents>
                        </SfDropDownList>
                    </ValueTemplate>
                </QueryBuilderTemplates>
            </QueryBuilderColumn>
            <QueryBuilderColumn Field="Category" Label="Category" Type=Syncfusion.Blazor.QueryBuilder.ColumnType.String></QueryBuilderColumn>
            <QueryBuilderColumn Field="Description" Label="Description" Type=ColumnType.String></QueryBuilderColumn>
            <QueryBuilderColumn Field="Amount" Label="Amount" Type=Syncfusion.Blazor.QueryBuilder.ColumnType.Number></QueryBuilderColumn>
        </QueryBuilderColumns>
    </SfQueryBuilder>
    <button @onclick="GetQueryAsSql">Get query as sql</button>
    <p>@QueryAsSql</p>
</div>
@code{
    protected SfQueryBuilder<ExpenseData> QueryBuilderRef { get; set; }
    public string DefaultValue = "Cash";
    public List<ExpenseData> DataSource;
    public class ExpenseData
    {
        public string Category { get; set; }
        public string PaymentMode { get; set; }
        public string Description { get; set; }
        public int Amount { get; set; }
    }
    protected override void OnInitialized()
    {
        DataSource = new List<ExpenseData>()
        {
            new ExpenseData() {Category= "Food", PaymentMode="Credit Card", Description="Boiled peanuts", Amount=100 },
            new ExpenseData() {Category= "Food", PaymentMode="Debit Card", Description="Boiled peanuts", Amount=200 },
            new ExpenseData() {Category= "Food", PaymentMode="Cash", Description="Confederate cush", Amount=300 },
            new ExpenseData() {Category= "Transportation", PaymentMode="Cash", Description="Public and other transportation", Amount=150 },
            new ExpenseData() {Category= "Transportation", PaymentMode="Debit Card", Description="Public and other transportation", Amount=250 }
        };
    }
    public class ItemFields
    {
        public string Id { get; set; }
    }
    public List<ItemFields> Items = new List<ItemFields>() {
        new ItemFields(){ Id= "Cash" },
        new ItemFields(){ Id= "Debit Card" },
        new ItemFields(){ Id= "Credit Card" },
        new ItemFields(){ Id= "Net Banking" }
    };
    public void OnChange(Syncfusion.Blazor.DropDowns.ChangeEventArgs<string, ItemFields> args, RuleModel Rule)
    {
        Rule.Value = args.Value;
    }
    // List<RuleModel> Rules = new List<RuleModel>()
    // {
    //     new RuleModel { Label="PaymentMode", Field="PaymentMode", Type="String", Operator="equal", Value="Debit Card" },
    //     new RuleModel { Label="Category", Field="Category", Type="String", Operator="equal", Value="Food" },
    //     new RuleModel { Label="Amount", Field="Amount", Type="Number", Operator="notequal" }
    // };
    protected Task QueryBuilderCreated()
    {
        // This works:
        // QueryBuilderRef.SetRulesFromSql("Category = 'Food'");


        // This fails with:
        // System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. (Parameter 'index')
        // at System.Collections.Generic.List`1.get_Item(Int32 index)
        // at Syncfusion.Blazor.QueryBuilder.Internal.SqlRules`1.ParseSqlString()
        QueryBuilderRef.SetRulesFromSql("PaymentMode= 'Debit Card'");


        // Get the rules & display in the UI as SQL
        var rules = QueryBuilderRef.GetRules();
        QueryAsSql = QueryBuilderRef.GetSqlFromRules(rules);
        return Task.CompletedTask;
    }


    public string QueryAsSql { get; set; }


    protected void GetQueryAsSql()
    {
        var rules = QueryBuilderRef.GetRules();
        if (rules is null)
        {
            QueryAsSql = "(null rules)";
        }
        else
        {
          QueryAsSql = QueryBuilderRef.GetSqlFromRules(rules);
        }
    }
}

[YuvanShankar Arunagiri]

Hi Darren,

We have validated your reported query and found a typo issue in your provided sample. please refer the below code snippet and Put space between the field name and operator.

protected Task QueryBuilderCreated()

    {

        QueryBuilderRef.SetRulesFromSql("PaymentMode = 'Debit Card'");

     }

Could you please check the above code and get back to us, if you need any further assistance on this. 

Regards,

YuvanShankar A

Attachment: Counter_ed4480c2.zip

[Darren]

Yes, that did fix that issue. I guess that means that you're not using a full sql query parser, just splitting by spaces? But, thanks for the resolution.

[YuvanShankar Arunagiri]

Hi Darren,

Thanks for the update. Yes, we have split the SQL query by space. Please let us know if you need further assistance on this.

Regards,

YuvanShankar A